CountIf

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

gtlca - 28 May 2008 20:01 GMT

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.

Signature

insanity is only a frame of mind

Reply to this Message

Luke M - 28 May 2008 21:07 GMT

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

Signature

Best Regards,

Luke M

> Hello out there.
>
[quoted text clipped - 26 lines]
>
> Thanks.

Reply to this Message

gtlca - 28 May 2008 21:32 GMT

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

Signature

insanity is only a frame of mind

> =SUMPRODUCT(--(E9:E25="MS"),--(H9:H25=9.7),--(H9:H25=19.5))
>
[quoted text clipped - 28 lines]
> >
> > Thanks.

Reply to this Message

gtlca - 29 May 2008 02:19 GMT

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

Signature

insanity is only a frame of mind

> =SUMPRODUCT(--(E9:E25="MS"),--(H9:H25=9.7),--(H9:H25=19.5))
>
[quoted text clipped - 28 lines]
> >
> > Thanks.

Reply to this Message

ND Pard - 28 May 2008 23:11 GMT

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.

> Hello out there.
>
[quoted text clipped - 26 lines]
>
> Thanks.

Reply to this Message

gtlca - 29 May 2008 02:24 GMT

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

Signature

insanity is only a frame of mind

> Assuming your data is in cells A2 through B100, try this formula:
>
[quoted text clipped - 39 lines]
> >
> > Thanks.

Reply to this Message

Teethless mama - 29 May 2008 04:08 GMT

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

> Hello out there.
>
[quoted text clipped - 26 lines]
>
> Thanks.

Reply to this Message

gtlca - 29 May 2008 13:06 GMT

Bingo!!!!! Thanks a bunch.

Signature

insanity is only a frame of mind

> =SUMPRODUCT((E9:E25="MS")*(H9:H25={9.7,19.5}))
>
[quoted text clipped - 28 lines]
> >
> > Thanks.

Reply to this Message