COUNTIF Alternative for multiple criteria?

COUNTIF.......no
COUNTIFS....yes!

With
Col_B containing the codes
Col_F containing the durations
Col_H containing the areas

And
J1: a code to match....eg Lunch
K1: an area to match...eg Retail
L1: a threshold to exceed....eg 3 min

This formula counts the number of instance where the Retail Area was late more than 3 minutes for a Code of Lunch:
M1: =COUNTIFS(B2:B30,J1,H2:H30,K1,F2:F30,">"&L1)

Adjust range references to suit your situation.
Alternatively, since you're using Excel 2007, you could reference entire columns:
M1: =COUNTIFS(B:B,J1,H:H,K1,F:F,">"&L1)

Does that help?

• The COUNTIFS (and SUMIFS, and some other functions) were added in Excel 2007 and later.
• If you post a unique name example and the results you want to see, we'll try to help.

Hi Ron,

Following my inital example, basically, in column A there are a list of names (duplicates in most cases) - is there a way this can be narrowed down to occurances over 3 minutes but only unique individuals? (Ie, if they have 3 occasions over 3 minutes that it only counts 1)?

Also, I would be using this work with Excel 2003 unfortunately - is there an alternative function that this formula would work with?

Many thanks for your help

Dan

OK...I understand: Your profile indicates Excel 2007, but, in this case you need a solution for Excel 2003.

However, I'm not really sure what you want the formula to do now.
For a count of the number of instances where the Retail Area was late more than 3 minutes for a Code of Lunch, try this regular formula (which will work in Pre-Excel 2007 versions):

Please Login or Register  to view this content.

And for the count of instances where a Col_A name:
• Appears only once
• Has a code of Lunch
• Has an area of Retail
• Is late more than 3 minutes.
Try this regular formula:

Please Login or Register  to view this content.

Is that something you can work with?
...If: No, can you post some sample data and what results you want to see (based on that data)?