Geek Answers Handbook

How to count in multiple columns?

x = 0 For Each cell in Sheets("01").Range("A:A").Cells If cell.Value = "aaa" And cell.Offset(0, 1).Value = "bbb" Then x = x+1 End If Next cell MsgBox x

Is there a shorter way, pls?

+8
excel-vba excel
source share
1 answer

You can do this with a formula like

(Excel 2007 or later)

 =COUNTIFS(A:A,"aaa",B:B,"bbb")

(Excel 2003 or earlier)

 =SUMPRODUCT(--(A:A="aaa")*--(B:B="bbb"))

Or, if you need to do this in VBA, use Evaluate

 MsgBox Evaluate("=COUNTIFS(A:A,""aaa"",B:B,""bbb"")") MsgBox Evaluate("=SUMPRODUCT(--(A:A=""aaa"")*--(B:B=""bbb""))")

EDIT based on your comment

If rDat and rSec are Range 's, and ct is a CodeName sheet, do it like this:

 Dim rDat As Range Dim rSec As Range Set rDat = ct.[A:A] Set rSec = ct.[B:B] ct.Range("C6").Value = Evaluate("=COUNTIFS(" & rDat.Address(, , , True) & " ,""a""," & rSec.Address(, , , True) & ",""1"")")

If ct is a Name sheet, use this

 Dim ws As Worksheet Dim rDat As Range Dim rSec As Range Set ws = ActiveWorkbook.Worksheets("ct") Set rDat = ws.[A:A] Set rSec = ws.[B:B] ws.Range("C6").Value = Evaluate("=COUNTIFS(" & rDat.Address(, , , True) & " ,""a""," & rSec.Address(, , , True) & ",""1"")")

If rDat and rSec are strings, use this

 Dim ws As Worksheet Dim rDat As String Dim rSec As String Set ws = ActiveWorkbook.Worksheets("ct") rDat = "ct!A:A" rSec = "ct!B:B" ws.Range("C6").Value = Evaluate("=COUNTIFS(" & rDat & " ,""a""," & rSec & ",""1"")")

Another option (for rDat , rSec as ranges)

 ct.Range("C6").Value = Application.WorksheetFunction.CountIfs(rDat, "a", rSec, "1")
+23
source share

More articles:


All Articles