Count Cells with Specific Text in Multiple Columns without Double Counting

jessebh2003

New Member
Joined
Feb 28, 2020
Messages
30
Office Version
  1. 365
Platform
  1. Windows
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:

Excel Formula:
=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."

Excel Formula:
=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:
Excel Formula:
=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!!
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Oddball2020

Board Regular
Joined
May 6, 2021
Messages
69
Office Version
  1. 365
Platform
  1. Windows
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:

Excel Formula:
=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."

Excel Formula:
=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:
Excel Formula:
=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

New Member
Joined
Feb 28, 2020
Messages
30
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
59,878
Office Version
  1. 365
Platform
  1. Windows
Personally I'd use helper columns, but you could do it like
Excel Formula:
=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

Board Regular
Joined
May 6, 2021
Messages
69
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Feb 28, 2020
Messages
30
Office Version
  1. 365
Platform
  1. Windows
"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

Board Regular
Joined
May 6, 2021
Messages
69
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Feb 28, 2020
Messages
30
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
May 6, 2021
Messages
69
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
May 6, 2021
Messages
69
Office Version
  1. 365
Platform
  1. Windows
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:

VBA Code:
 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
 

Forum statistics

Threads
1,136,258
Messages
5,674,668
Members
419,520
Latest member
Jennifer4Dillon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top