How to use COUNTIFS and COUNTIF with multiple criteria using dates

You won't be able to use COUNTIF, as that is for single conditions.

You could use COUNTIFS like this:

=COUNTIFS(J:J,"<="&TODAY(),F:F,"<>c*")

but only with Excel versions from 2007 or later - your profile shows that yo are using Excel 2003, so please update this if it is no longer valid.

Note that the asterisk is the wildcard character so it is just looking for the first letter in column F beginning with "c", but if you have other text which also begins with c then the formula will fail.

Hope this helps.

Pete

Originally Posted by Sam Capricci

in the absence of any additional information you could use a sumproduct for this, like this...
=SUMPRODUCT((J2:J6<=TODAY())*(F2:F6<>"complete")*(F2:F6<>"closed"))
this will count all items in J2 to J6 where they are less than or equal to today and not equal to either complete or closed in col F.
adjust from there.

Thank you Sam Capricci! This worked!

Originally Posted by Pete_UK

You won't be able to use COUNTIF, as that is for single conditions.

You could use COUNTIFS like this:

=COUNTIFS(J:J,"<="&TODAY(),F:F,"<>c*")

but only with Excel versions from 2007 or later - your profile shows that yo are using Excel 2003, so please update this if it is no longer valid.

Note that the asterisk is the wildcard character so it is just looking for the first letter in column F beginning with "c", but if you have other text which also begins with c then the formula will fail.

Hope this helps.

Pete

Thank you Pete! This also worked!