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

[cledus]

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.

 

[Rick Rothstein]

What's wrong with simply adding them together...

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

 

[JoeMo]

Or, if you want a single formula:
=SUMPRODUCT(--(Questions!H6:H94="Y")+--(Questions!H6:H94="N"))

 

[Rick Rothstein]

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.

 

[JoeMo]

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 !

 

[Rick Rothstein]

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 !

 

[cledus]

What's wrong with simply adding them together...

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

That did it! Thanks and it figures I was missing as simple as this.