Count Cells with Specific Text in Multiple Columns without Double Counting

jessebh2003

Board Regular
Joined
Feb 28, 2020
Messages
71
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!!
 
Another option, which will count yes, no & both & output the results in J9:K11 on the summary sheet
VBA Code:
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 = 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
   Sheets("Summary").Range("J9:K11").Value = Application.Transpose(Array(Array("Yes", "No", "Both"), Nary))
End Sub
 
Upvote 0

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Another option, which will count yes, no & both & output the results in J9:K11 on the summary sheet
VBA Code:
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 = 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
   Sheets("Summary").Range("J9:K11").Value = Application.Transpose(Array(Array("Yes", "No", "Both"), Nary))
End Sub
Nice, but I think he said his list length would be dynamic each time?
 
Upvote 0
The OP said it would never exceed 1000 rows, so I hardcoded that range as the extra time taken to process blank rows should be negligible, but it can always be made dynamic if the OP wishes.
 
Upvote 0
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

Hi Oddball2020,

This gave me a zero result but there are five Yes responses within those columns - AQ3, BA10, CO30, CO101, CO133.

1621858822876.png
 
Upvote 0
Another option, which will count yes, no & both & output the results in J9:K11 on the summary sheet
VBA Code:
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 = 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
   Sheets("Summary").Range("J9:K11").Value = Application.Transpose(Array(Array("Yes", "No", "Both"), Nary))
End Sub

Hi Fluff! Thanks for your suggestion too. This gave me zero answers as well but there are Yes responses in AQ3, BA10, CO30, CO101 and CO133 and No responses in AQ5 and BA4.

1621859060338.png
 
Upvote 0
Can you post a small example of your data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
 
Upvote 0
Can you post a small example of your data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Here you go. Thanks!

ohiohealthevaluation593520210524080744.xlsx
BCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBFBGBHBIBJBKBLBMBNBOBPBQBRBSBTBUBVBWBXBYBZCACBCCCDCECFCGCHCICJCKCLCMCNCOCPCQCRCSCTCUCVCWCXCYCZDADBDCDDDEDFDGDHDIDJDKDLDMDNDODPDQDRDSDTDUDVDWDXDYDZEAEBECEDEEEFEGEHEIEJEKELEMENEOEPEQERESETEU
1Evaluation for 16th Annual OhioHealth Capital City Sports Medicine VIRTUAL Symposium
2The content was relevant to my workThis event provided me with new information (or clarified existing Information)I intend to use what I learned from this activityIf you agree with the previous statement, please provide us with details as to which component(s) of the conference you plan on using.The speaker was knowledgeable about the subjectThe speaker was effective in helping me learn new information (or clarifying existing information)The speaker was responsive to participant's needs and questionsThe presentation(s) was well organized, clear and conciseAdditional Speaker Comments:Overall, the conference was free of biasThe learning environment was conducive to learningWhat other learning formats would have been helpful for this activity?If other, please specify.Please provide us with suggestions for improving the content, facilitation, delivery, environment and/or utility of this event.Objective 1Objective 2Objective 3Objective 4Objective 5Objective 6Objective 7Objective 8Objective 9Objective 10Objective 11Objective 12Objective 14Objective 14Objective 15Objective 16Objective 17Objective 18Objective 19Objective 20Commitment to Change AreasPlease identify any barriers you perceive in implementing these changes:If other barrier, please specify:If you do not plan to make a change in your performance, please indicate the reason below:If other reason, please specify:Clinical Assessment/Physical Exam - List the specific, measurable change(s) you plan to make:Clinical Assessment/Physical Exam - How confident are you that you will be able to make this change?Clinical Assessment/Physical Exam - Have you been able to implement the change(s) listed above?Clinical Assessment/Physical Exam - Briefly describe the outcome of implementing the change(s) in terms of how it affected your practice, team or patient outcomes:Clinical Assessment/Physical Exam - PROVIDER:Clinical Assessment/Physical Exam - TEAM:Clinical Assessment/Physical Exam - PATIENT:Clinical Assessment/Physical Exam - SYSTEM/ORGANIZATION:Clinical Assessment/Physical Exam - OTHER:Clinical Assessment/Physical Exam - What might you do to address barriers you encountered? Please indicate your next steps:Diagnosis - List the specific, measurable change(s) you plan to make:Diagnosis - How confident are you that you will be able to make this change?Diagnosis - Have you been able to implement the change(s) listed above?Diagnosis - Briefly describe the outcome of implementing the change(s) in terms of how it affected your practice, team or patient outcomes:Diagnosis - PROVIDER:Diagnosis - TEAM:Diagnosis - PATIENT:Diagnosis - SYSTEM/ORGANIZATION:Diagnosis - OTHER:Diagnosis - What might you do to address barriers you encountered? Please indicate your next steps:Patient Communication - List the specific, measurable change(s) you plan to make:Patient Communication - How confident are you that you will be able to make this change?Patient Communication - Have you been able to implement the change(s) listed above?Patient Communication - Briefly describe the outcome of implementing the change(s) in terms of how it affected your practice, team or patient outcomes:Patient Communication - PROVIDER:Patient Communication - TEAM:Patient Communication - PATIENT:Patient Communication - SYSTEM/ORGANIZATION:Patient Communication - OTHER:Patient Communication - What might you do to address barriers you encountered? Please indicate your next steps:Increase/Decrease Testing - List the specific, measurable change(s) you plan to make:Increase/Decrease Testing - How confident are you that you will be able to make this change?Increase/Decrease Testing - Have you been able to implement the change(s) listed above?Increase/Decrease Testing - Briefly describe the outcome of implementing the change(s) in terms of how it affected your practice, team or patient outcomes:Increase/Decrease Testing - PROVIDER:Increase/Decrease Testing - TEAM:Increase/Decrease Testing - PATIENT:Increase/Decrease Testing - SYSTEM/ORGANIZATION:Increase/Decrease Testing - OTHER:Increase/Decrease Testing - What might you do to address barriers you encountered? Please indicate your next steps:Formulate Plan of Care - List the specific, measurable change(s) you plan to make:Formulate Plan of Care - How confident are you that you will be able to make this change?Formulate Plan of Care - Have you been able to implement the change(s) listed above?Formulate Plan of Care - Briefly describe the outcome of implementing the change(s) in terms of how it affected your practice, team or patient outcomes:Formulate Plan of Care - PROVIDER:Formulate Plan of Care - TEAM:Formulate Plan of Care - PATIENT:Formulate Plan of Care - SYSTEM/ORGANIZATION:Formulate Plan of Care - OTHER:Formulate Plan of Care - What might you do to address barriers you encountered? Please indicate your next steps:New Prescription - List the specific, measurable change(s) you plan to make:New Prescription - How confident are you that you will be able to make this change?New Prescription - Have you been able to implement the change(s) listed above?New Prescription - Briefly describe the outcome of implementing the change(s) in terms of how it affected your practice, team or patient outcomes:New Prescription - PROVIDER:New Prescription - TEAM:New Prescription - PATIENT:New Prescription - SYSTEM/ORGANIZATION:New Prescription - OTHER:New Prescription - What might you do to address barriers you encountered? Please indicate your next steps:Change Prescription - List the specific, measurable change(s) you plan to make:Change Prescription - How confident are you that you will be able to make this change?Change Prescription - Have you been able to implement the change(s) listed above?Change Prescription - Briefly describe the outcome of implementing the change(s) in terms of how it affected your practice, team or patient outcomes:Change Prescription - PROVIDER:Change Prescription - TEAM:Change Prescription - PATIENT:Change Prescription - SYSTEM/ORGANIZATION:Change Prescription - OTHER:Change Prescription - What might you do to address barriers you encountered? Please indicate your next steps:Increase Dose - List the specific, measurable change(s) you plan to make:Increase Dose - How confident are you that you will be able to make this change?Increase Dose - Have you been able to implement the change(s) listed above?Increase Dose - Briefly describe the outcome of implementing the change(s) in terms of how it affected your practice, team or patient outcomes:Increase Dose - PROVIDER:Increase Dose - TEAM:Increase Dose - PATIENT:Increase Dose - SYSTEM/ORGANIZATION:Increase Dose - OTHER:Increase Dose - What might you do to address barriers you encountered? Please indicate your next steps:Decrease Dose - List the specific, measurable change(s) you plan to make:Decrease Dose - How confident are you that you will be able to make this change?Decrease Dose - Have you been able to implement the change(s) listed above?Decrease Dose - Briefly describe the outcome of implementing the change(s) in terms of how it affected your practice, team or patient outcomes:Decrease Dose - PROVIDER:Decrease Dose - TEAM:Decrease Dose - PATIENT:Decrease Dose - SYSTEM/ORGANIZATION:Decrease Dose - OTHER:Decrease Dose - What might you do to address barriers you encountered? Please indicate your next steps:Education for Patient/Family/Self - List the specific, measurable change(s) you plan to make:Education for Patient/Family/Self - How confident are you that you will be able to make this change?Education for Patient/Family/Self - Have you been able to implement the change(s) listed above?Education for Patient/Family/Self - Briefly describe the outcome of implementing the change(s) in terms of how it affected your practice, team or patient outcomes:Education for Patient/Family/Self - PROVIDER:Education for Patient/Family/Self - TEAM:Education for Patient/Family/Self - PATIENT:Education for Patient/Family/Self - SYSTEM/ORGANIZATION:Education for Patient/Family/Self - OTHER:Education for Patient/Family/Self - What might you do to address barriers you encountered? Please indicate your next steps:Specify other area:Other (specify area) - List the specific, measurable change(s) you plan to make:Other (specify area) - How confident are you that you will be able to make this change?Other (specify area) - Have you been able to implement the change(s) listed above?Other (specify area) - Briefly describe the outcome of implementing the change(s) in terms of how it affected your practice, team or patient outcomes:Other (specify area) - PROVIDER:Other (specify area) - TEAM:Other (specify area) - PATIENT:Other (specify area) - SYSTEM/ORGANIZATION:Other (specify area) - OTHER:Other (specify area) - What might you do to address barriers you encountered? Please indicate your next steps:
3444Plantar Fasciitis, Voltaren Gel, Posterior Shoulder examination and going into my detail with it than assuming it’s just spasm or strain, cross training schedules 444434Case_Study Demonstration Focus_Group Roundtable_DiscussionsAssessment Diagnosis EducationLimited_Resources_(Funding,_Equipment,_Cost_of_Treatment) HabitUsing our PT’s and other colleagues more often to help with further evaluations when they are available. 10YesI tend to treat a lot of my injuries the same and base it off pain and progression. I need to learn more about focusing on a specific area and just adding the area as a whole later. 10Using pictures, explaining MRI and XRay results in a way that the patient will understand. Also, explaining the process we may have to go through for a specific problem. 10
4444I have been more educated on the care for transgender athletes. As well as applying the information I learned from the presentations in my work.445445N/AMake sure the link is emailed to the participants the day before for the participants.CommunicationLimited_Opportunity_(Patients)Making sure preferred pronouns are used and have a space on evaluation sheet for patients.10
5555Looking at plantar fasciitis differently. How I treat pediatric knees. Shoulder instability. How patient return to sports555554Demonstration Roundtable_DiscussionsAssessment EducationLimited_Resources_(Funding,_Equipment,_Cost_of_Treatment) Colleague/System_Not_Supportive_of_Change HabitReview with patients on what we are trying to do10Make sure everyone is ok with the plan and can be on board9
6555Acl supported ebb course I just finished, great validation. Really enjoyed the topics today.5555Great55WebinarLoved, loved this format, I live where I usually have to get up way early to get there, I could sleep to normal time and enjoy my coffee, was way more attentive and got a lot out of itTesting FormulateLimited_Opportunity_(Patients)Educate athlete and family more10More testing at end10
Data
Cell Formulas
RangeFormula
B1B1=$A$3
 
Upvote 0
None of those cells have either Yes or No in the relevant columns.
 
Upvote 0

Forum statistics

Threads
1,215,425
Messages
6,124,825
Members
449,190
Latest member
rscraig11

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
Back
Top