# Count Cells with Specific Text in Multiple Columns without Double Counting

#### jessebh2003

##### New Member
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
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
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
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

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

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

Replies
12
Views
127
Replies
17
Views
467
Replies
6
Views
235
Replies
0
Views
167
Replies
10
Views
151

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

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