Count Cells with Specific Text in Multiple Columns without Double Counting

[jessebh2003]

I'm trying to write a formula that will count the number of rows that contain "Yes" or "No" in multiple columns. However, if more than one column for the same row contains "Yes" or "No" I only want the row to be counted once. Here's what I've tried:

=SUM(ISNUMBER(MATCH(Data!$AQ$3:$AQ$1000, Data!$BA$3:$BA$1000, Data!$BK$3:$BK$1000, Data!$BU$3:$BU$1000, Data!$CE$3:$CE$1000, Data!$CD$3:$CD$1000, Data!$CV$3:$CV$1000, Data!$DI$3:$DI$1000, Data!$DS$3:$DS$1000, Data!$EC$3:$EC$1000, Data!$EN$3:$EN$1000{"Yes","No"},0)))

This gives me the error "You've entered too many arguments for this function."

=SUMPRODUCT(--((Data!$AQ$3:$AQ$1000 = "Yes","No")+(Data!$BA$3:$BA$1000 = "Yes","No")+(Data!$BK$3:$BK$1000 = "Yes","No")+(Data!$BU$3:$BU$1000 = "Yes","No")+(Data!$CE$3:$CE$1000 = "Yes","No")+(Data!$CD$3:$CD$1000 = "Yes","No")+(Data!$CV$3:$CV$1000 = "Yes","No")+(Data!$DI$3:$DI$1000 = "Yes","No")+(Data!$DS$3:$DS$1000 = "Yes","No")+(Data!$EC$3:$EC$1000 = "Yes","No")+(Data!$EN$3:$EN$1000 ="Yes","No"),>0))

This gives me a 0 even though there is text in the specified columns.

Both of these codes Then I need to count all of the cells in the came columns that contain "Yes" and then "No". So I tried:

=COUNTIFS(Data!$AQ$3:$AQ$1000,Data!$BA$3:$BA$1000,Data!$BK$3:$BK$1000,Data!$BU$3:$BU$1000,Data!$CE$3:$CE$1000,Data!$CD$3:$CD$1000,Data!$CV$3:$CV$1000,Data!$DI$3:$DI$1000,Data!$DS$3:$DS$1000,Data!$EC$3:$EC$1000,Data!$EN$3:$EN$1000,"Yes")

This gave me an alert saying that the formula returned multiple results and spilled over into other cells giving me 998 cells of 0.

Can anyone help me fix these? Thanks!!

 

[Fluff]

Oops, missed that your data started in B1 & not A1.
Having changed that I get

+Fluff 1.xlsm
	J	K
9	Yes	1
10	No	0
11	Both	0
Summary

Check that your cells with Yes or No don't have any leading/trailing spaces or other hidden characters.

 

[jessebh2003]

Oops, missed that your data started in B1 & not A1.
Having changed that I get

+Fluff 1.xlsm
	J	K
9	Yes	1
10	No	0
11	Both	0
Summary

Check that your cells with Yes or No don't have any leading/trailing spaces or other hidden characters.

I checked and there are no spaces or other characters and ran the macro again and still got zeros. I even repasted the code into the module but still zeros.

TestEvalData.xlsx
	J	K
9	Yes	0
10	No	0
11	Both	0
Summary

TestEvalData.xlsm
	J	K
9	Yes	0
10	No	0
11	Both	0
Summary

 

[Fluff]

Do you have the code in a standard module, or in a Sheet Module or the ThisWorkbook Module?

 

[jessebh2003]

Do you have the code in a standard module, or in a Sheet Module or the ThisWorkbook Module?

I believe the code is in a standard module under VBAProject (PERSONAL.XLSB), Modules.

 

[Fluff]

Ok, what if you try

Sub jessebh()
   Dim Ary As Variant, Nary(1 To 3) As Long
   Dim r As Long, c As Long
   Dim y As Boolean, n As Boolean
   
   Ary = ActiveWorkbook.Sheets("Data").Range("AQ3:EN1000").Value2
   For r = 1 To UBound(Ary)
      For c = 1 To 101 Step 10
         If LCase(Ary(r, c)) = "yes" And Not y Then
            Nary(1) = Nary(1) + 1
            If n Then Nary(3) = Nary(3) + 1
            y = True
         ElseIf LCase(Ary(r, c)) = "no" And Not n Then
            Nary(2) = Nary(2) + 1
            If y Then Nary(3) = Nary(3) + 1
            n = True
         End If
         If y And n Then Exit For
      Next c
      y = False
      n = False
   Next r
   ActiveWorkbook.Sheets("Summary").Range("J9:K11").Value = Application.Transpose(Array(Array("Yes", "No", "Both"), Nary))
End Sub

 

[jessebh2003]

Ok, what if you try

Sub jessebh()
   Dim Ary As Variant, Nary(1 To 3) As Long
   Dim r As Long, c As Long
   Dim y As Boolean, n As Boolean
  
   Ary = ActiveWorkbook.Sheets("Data").Range("AQ3:EN1000").Value2
   For r = 1 To UBound(Ary)
      For c = 1 To 101 Step 10
         If LCase(Ary(r, c)) = "yes" And Not y Then
            Nary(1) = Nary(1) + 1
            If n Then Nary(3) = Nary(3) + 1
            y = True
         ElseIf LCase(Ary(r, c)) = "no" And Not n Then
            Nary(2) = Nary(2) + 1
            If y Then Nary(3) = Nary(3) + 1
            n = True
         End If
         If y And n Then Exit For
      Next c
      y = False
      n = False
   Next r
   ActiveWorkbook.Sheets("Summary").Range("J9:K11").Value = Application.Transpose(Array(Array("Yes", "No", "Both"), Nary))
End Sub

That worked great! Here's what I got.

TestEvalData.xlsm
	J	K
9	Yes	8
10	No	5
11	Both	2
Summary

I then added the formula

=SUM(K9:K11)-(K11*2)

to get my total rows without double counting. Thank you so much for your help with this! I truly appreciate it!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Oddball2020]

There is a Yes in AQ3.

<deleted>

Saw that it was resolved!