CountIf
 

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:H259.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.




--
insanity is only a frame of mind

CountIf
 

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

Luke M


"gtlca" wrote:

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:H259.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.




--
insanity is only a frame of mind

CountIf
 

I keyed this in but my result was -0-, when I know (for my real data) it
should be 2.
--
insanity is only a frame of mind


"Luke M" wrote:

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

Luke M


"gtlca" wrote:

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:H259.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.




--
insanity is only a frame of mind

CountIf
 

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" wrote:

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:H259.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.




--
insanity is only a frame of mind

CountIf
 

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
--
insanity is only a frame of mind


"Luke M" wrote:

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

Luke M


"gtlca" wrote:

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:H259.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.




--
insanity is only a frame of mind

CountIf
 

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
--
insanity is only a frame of mind


"ND Pard" wrote:

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" wrote:

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:H259.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.




--
insanity is only a frame of mind

CountIf
 

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


"gtlca" wrote:

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:H259.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.




--
insanity is only a frame of mind

CountIf
 

Bingo!!!!! Thanks a bunch.
--
insanity is only a frame of mind


"Teethless mama" wrote:

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


"gtlca" wrote:

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:H259.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.




--
insanity is only a frame of mind