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!!

 

[Oddball2020]

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!!

I tend to do things in a more simple way. Will it work to just auto-fill this formula down another column (I used Col A) something like this:

=IF(COUNTIF(B2:P3, "Yes")>0, "Yes", "No")

which is just using COUNTIF to see if there's any "Yes" on that row, and if so, then say yes.

Then your total formula is just counting up those Yes in that Column A, because it only displayed a single Yes despite how many Yes's were on that row.

Then there's no need to fiddle with a sumproduct and try to make it work in a single formula cell.

 

[jessebh2003]

I tend to do things in a more simple way. Will it work to just auto-fill this formula down another column (I used Col A) something like this:

=IF(COUNTIF(B2:P3, "Yes")>0, "Yes", "No")

which is just using COUNTIF to see if there's any "Yes" on that row, and if so, then say yes.

Then your total formula is just counting up those Yes in that Column A, because it only displayed a single Yes despite how many Yes's were on that row.

Then there's no need to fiddle with a sumproduct and try to make it work in a single formula cell.

I'm going to be adding the formula to a macro so I'd prefer to only have to write a couple formulas versus many - 1) count rows with a Yes or No in the specified columns without a row more than once, 2) count all of the cells with Yes responses regardless of the number of times Yes is in a row and 3) count all of the cells with No responses regardless of the number of times No is in a row.

What I'm trying to achieve is first, count the number of respondents that answered Yes or No in up to three columns. Second, count the number of Yes responses. Third, count the number of No responses. One respondent may have up to three responses in any of the specific columns. Apologies if that was confusing.

 

[Fluff]

Personally I'd use helper columns, but you could do it like

=COUNT(FILTER(ROW(Data!$AQ$3:$AQ$1000),(Data!$AQ$3:$AQ$1000="yes")+(Data!$AQ$3:$AQ$1000="no")+(Data!$BA$3:$BA$1000="yes")+(Data!$BA$3:$BA$1000="no")+(Data!$BK$3:$BK$1000="yes")+(Data!$BK$3:$BK$1000="yes")))

 

[Oddball2020]

I'm going to be adding the formula to a macro so I'd prefer to only have to write a couple formulas versus many - 1) count rows with a Yes or No in the specified columns without a row more than once, 2) count all of the cells with Yes responses regardless of the number of times Yes is in a row and 3) count all of the cells with No responses regardless of the number of times No is in a row.

What I'm trying to achieve is first, count the number of respondents that answered Yes or No in up to three columns. Second, count the number of Yes responses. Third, count the number of No responses. One respondent may have up to three responses in any of the specific columns. Apologies if that was confusing.

"I'm going to be adding the formula to a macro..." if you're going to use VBA, then why not just do all the counting on the VBA side completely in a loop for each row, rolling up the tally for Yes and No towards the final total? Is this an option?

 

[jessebh2003]

"I'm going to be adding the formula to a macro..." if you're going to use VBA, then why not just do all the counting on the VBA side completely in a loop for each row, rolling up the tally for Yes and No towards the final total? Is this an option?

It could be. Can you tell me how?

 

[Oddball2020]

It could be. Can you tell me how?

I'd need to know which columns you wanted counted, the sheet name, which rows (if dynamic, and # row changes, then what column to use to determine last row), and where the headers are (just row 1?). Also a cell to temp store the math in as the loop rolls up. This cell can be empty and will get cleared out at the end of it. Also, everything is either Yes or No correct in those columns, not blanks?

 

[jessebh2003]

Hi Oddball,

That would be amazing! THANK YOU!

Columns to be counted: AQ, BA, BK, BU, CE, CO, CY, DI, DS, EC, EN
Sheet Name: Data
Rows: 3 - 1000 (the number of rows will vary each time but wouldn't exceed 1000)
Last Column: EU
Header Row: 2
Cell Contents: Yes, No, Blank

Cell to Store Math: L9
Cell Where Math Answer will Go: J9
Sheet Name: Summary

 

[Oddball2020]

Hi Oddball,

That would be amazing! THANK YOU!

Columns to be counted: AQ, BA, BK, BU, CE, CO, CY, DI, DS, EC, EN
Sheet Name: Data
Rows: 3 - 1000 (the number of rows will vary each time but wouldn't exceed 1000)
Last Column: EU
Header Row: 2
Cell Contents: Yes, No, Blank

Cell to Store Math: L9
Cell Where Math Answer will Go: J9
Sheet Name: Summary

Ok thanks, I'll put something together for you today. Should have posted by tomorrow.

 

[Oddball2020]

Hi Oddball,

That would be amazing! THANK YOU!

Columns to be counted: AQ, BA, BK, BU, CE, CO, CY, DI, DS, EC, EN
Sheet Name: Data
Rows: 3 - 1000 (the number of rows will vary each time but wouldn't exceed 1000)
Last Column: EU
Header Row: 2
Cell Contents: Yes, No, Blank

Cell to Store Math: L9
Cell Where Math Answer will Go: J9
Sheet Name: Summary

Ok, this was a lot easier/simplier than I thought. This works for me, but is currently only counting the Yes remarks. You can set up other sections for No's as well. Let me know if this works for you:

 Sheets("Data").Select

    Dim AQ, BA, BK, BU, CE, CO, CY, DI, DS, EC, EN As String
    
    Dim lastRow, i, TOT, PTOT, NTOT As Long
    lastRow = ActiveSheet.Cells(Rows.Count, 43).End(xlUp).Row   'Uses Column AQ (43) to determine the last row on the Data sheet, change as needed
    
    Range("L9").Clearcontents 'clears out any lingering previous numbers that may still be residing in this cell

    For i = 3 To lastRow   'You said the header was in row 2, so the loop starts in row 3 through the LastRow based on the length in Col AQ     
   
    PTOT = Range("L9").Value   'this is the value of the total previously stored in L9
       
    'Count the appropriate columns for the i row for "Yes"
    AQ = Application.WorksheetFunction.CountIf(ActiveSheet.Range("AQ" & i), "Yes")
    BA = Application.WorksheetFunction.CountIf(ActiveSheet.Range("BA" & i), "Yes")
    BK = Application.WorksheetFunction.CountIf(ActiveSheet.Range("BK" & i), "Yes")
    BU = Application.WorksheetFunction.CountIf(ActiveSheet.Range("BU" & i), "Yes")
    CE = Application.WorksheetFunction.CountIf(ActiveSheet.Range("CE" & i), "Yes")
    CO = Application.WorksheetFunction.CountIf(ActiveSheet.Range("CO" & i), "Yes")
    CY = Application.WorksheetFunction.CountIf(ActiveSheet.Range("CY" & i), "Yes")
    DI = Application.WorksheetFunction.CountIf(ActiveSheet.Range("DI" & i), "Yes")
    DS = Application.WorksheetFunction.CountIf(ActiveSheet.Range("DS" & i), "Yes")
    EC = Application.WorksheetFunction.CountIf(ActiveSheet.Range("EC" & i), "Yes")
    EN = Application.WorksheetFunction.CountIf(ActiveSheet.Range("EN" & i), "Yes")
  
    TOT = AQ + BA + BK + BU + CE + CO + CY + DI + DS + EC + EN  'sums up all the Yes counts
   
    NTOT = PTOT + TOT  'adds the Total above to the previous total value in L9
   
    Range("L9").Value = NTOT  'places this new total in L9, to be used for the next iteration
   
    Next i
   
    Range("J9").Value = NTOT  'places the final value from L9 into J9
   
    Range("L9").ClearContents  'clears L9
   
    MsgBox ("Complete! " & NTOT & " Yes were counted!")   'change however you want, but it's nice for the macro to let you know it's done