Counting with formulas to meet specific conditions

[Greemo]

Hello,

I'm using the COUNTIFS formula but am having trouble getting the count to flow. Here is what I have currently:

=COUNTIFS(B:B,"ground",C:C,"6/28/2014",D:D,GROUNDSTAFF)

Where:
- Column B contains different levels. I only need to count those that are on the "ground" level
- Column C has different expiration dates. I only need to count those that expire on 6/28/2014
- Column D Contains the initials of a few different staff. I need to count several different staff (JB, MJ, and JG)

I created a custom named range named GROUNDSTAFF that points to cells which have the specific initials I want to count, but my count always returns zero.

I.e. - I want to count all the ground levels that expire 6/28/2014 from only the staff JB, MJ, and JG. Is there a way around my issue while still using 1 formula?

Thank you,

Greemo

 

[AhoyNC]

See if this works:

=SUMPRODUCT(COUNTIFS(B:B,"ground",C:C,"6/28/2014",D:D,GROUNDSTAFF))

 

[Greemo]

This works great! But why the heck does it?

Greemo

 

[AhoyNC]

When you added GROUNDSTAFF it became an array formula. If you take your original formula and highlight it in the formula bar and press F9 (hit esc before you move to another cell) you will see something like {1,1,0,1,0,1}.
The SUMPRODUCT will add the ones to give your count.

 

[Greemo]

Oh! Thank you!

Greemo

 

[Greemo]

When you added GROUNDSTAFF it became an array formula. If you take your original formula and highlight it in the formula bar and press F9 (hit esc before you move to another cell) you will see something like {1,1,0,1,0,1}.
The SUMPRODUCT will add the ones to give your count.

Hi AhoyNC,

I want to extend my table by adding another row (column E). This column would tell me if the staff initials from the table is part of the GROUNDSTAFF range.

How do I go about this? Something like?:
=IF(D2=GROUNDSTAFF,"On Groundstaff","")

Greemo

 

[AhoyNC]

Try

=IF(ISNUMBER(MATCH(D2,GROUNDSTAFF,0)),"On Groundstaff","")

 

[Greemo]

Perfect! Thank you.

Greemo

 

[AhoyNC]

You're welcome. Thanks for the feedback.