The Conundrum - Count occurences of multiple text values in single column

cledus

New Member
Joined
Apr 4, 2014
Messages
13
After working on this for 2 hours I decided to try community help/assistance for the first time and I'd greatly appreciate help on this.

The Conundrum


I'd trying several variations of COUNTIFS and SUMIFS. I'm attempting to determine the total number of times the text value of Y or N was entered in a single column.

I'm able to get the total # of occurrences for the text value of Y, which was 35, by using this formula.

=COUNTIF(Questions!H6:H94,"Y")

I'm able to get the total # of occurrences for the text value of N, which was 42, by using this formula.

=COUNTIF(Questions!H6:H94,"N")

Now I am trying to figure out how to combine those into a single formula that would get me a total # of occurrences for the text value of Y & N in a single column, which would be 76. Here's the formulas I've attempted to use.

=SUMIFS(Questions!H6:H94,"Y",Questions!H6:H94,"N")

=COUNTIFS(Questions!H6:H94,"Y",Questions!H6:H94,"N")

When I attempt to use in excel it does nothing. Again, I'd appreciate help on this. ;)
 
What's wrong with simply adding them together...

=COUNTIF(Questions!H6:H94,"Y")+COUNTIF(Questions!H6:H94,"N")
 
Upvote 0
Or, if you want a single formula:
=SUMPRODUCT(--(Questions!H6:H94="Y")+--(Questions!H6:H94="N"))
 
Upvote 0
Or, if you want a single formula:
=SUMPRODUCT(--(Questions!H6:H94="Y")+--(Questions!H6:H94="N"))
If you are going to add them together (rather than use comma notation version of SUMPRODUCT), then you do not need either of the double minus signs). I'm wondering, though, if SUMPRODUCT, being an array-processing formula, would be more or less efficient than two COUNTIF function calls? I sure wish there was some kind of "speed chart" for the various Excel functions so we could gauge there relative advantages.
 
Upvote 0
If you are going to add them together (rather than use comma notation version of SUMPRODUCT), then you do not need either of the double minus signs). I'm wondering, though, if SUMPRODUCT, being an array-processing formula, would be more or less efficient than two COUNTIF function calls? I sure wish there was some kind of "speed chart" for the various Excel functions so we could gauge there relative advantages.
Yes, the double minus signs are not necessary. If you are a slow typist (like me) you might opt for the single array formula which, sans the double minuses, has 39 characters compared to 41 for the two COUNTIF functions, and live with any potential efficiency hit :laugh:!
 
Upvote 0
If you are a slow typist (like me) you might opt for the single array formula which, sans the double minuses, has 43 characters compared to 45 for the two COUNTIF functions, and live with any potential efficiency hit :laugh:!

:rofl:
 
Upvote 0

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