CountIf

[gtlca]

Hello out there.

I need a formula to count a specific occurrence of an abbreviation, with two
criteria being met.

For example

How many MS students were there in the Fall? Same question would be posed
about PhD.

Col 1 = either MS or PhD, Col 2 = 9.7 (reflects Spring), 9.8(reflects
Fall), 19.5(reflects both Fall and Spring)

MS 9.7
MS 9.8
PhD 19.5
PhD 19.5
PhD 9.8

In scanning other threads, I was able to get the below formula to work for
"How many MS (or PhD) in Fall (as Fall would be greater than 9.7).

=SUMPRODUCT(--(E9:E25="MS"),--(H9:H25>9.7))

However, I cannot figure out the formula to show criteria for Spring, which
would include 9.7 and 19.5.

This is my first ever post - I hope I have included enough info, clearly.

Thanks.

 

[Luke M]

=SUMPRODUCT(--(E9:E25="MS"),--(H9:H25=9.7),--(H9:H25=19.5))

 

[gtlca]

I keyed this in but my result was -0-, when I know (for my real data) it
should be 2.

 

[ND Pard]

Assuming your data is in cells A2 through B100, try this formula:

The following array function might work.
Copy the following a cell.

=Count(If($A2:$A100="MS",If($B2:B100=9.8,$B2:$B100)))

Finally, because it is an array function,
hold down the Cntr and Shift key, then press Enter.
This will enclose the function in braces { }.

Good Luck.

 

[gtlca]

I was hopeful, but the calc didn't provide the correct answer; it returned
-0-. It seems like such a simple calc, but I am a bit of a novice on
functions. Not giving up.

gtlca

 

[gtlca]

Thank you for the suggestion, but the calc returned an incorrect answer.
Should there be the variable of "19.5" in the last part of the formula? Very
thankful...


gtlca

 

[Teethless mama]

=SUMPRODUCT((E9:E25="MS")*(H9:H25={9.7,19.5}))

 

[gtlca]

Bingo!!!!! Thanks a bunch.