Help! I need to determine the formula for using countif with 3 conditions: Example :

Column D should count these conditions:

1.Countif a:b <=January 31, 2009

2.Countif a:b = "DSSL"

3.Countif a:b = "DSSI"Column E should Count these conditions:

1.Countif a:b >January 31, 2009

2.Countif a:b = "DSSL"

3.Countif a:b = "DSSI"

Thanks!!

Just add them together. =COUNTIF(A:B, "<=January 31, 2009") +COUNTIF(A:B, "=DSSL")+COUNTIF(A:B, "=DSSI")

i tried doing it but it doesn't work.

Here's what my problem seems like:Column A (Type) Column B (Date)

NS 01/31/09

AC 01/28/09

DSSL 01/29/09

NS 01/28/09

DSSI 02/01/09

DSSL 02/01/09

NS 01/31/09

DSSI 01/31/09*Column D shoould count all DSSL and DSSI on or before 01/31/09

*Column E should count all DSSL and DSSI 02/01/09 onwards.

Please help me :(

you need to use SUMPRODUCT. Actually you don't have to use it, but I find it much easier than array formulae. =SUMPRODUCT(--($A1:$A$1000="DSSI"),--($B$1:$B$1000)<=DATEVALUE("01/31/09"))+ SUMPRODUCT((--($A1:$A$1000="DSSL"),--($B$1:$B$1000)<=DATEVALUE("01/31/09"))I've added them together as the first bit adds up the DSSIs, then the second adds up the DSSLs that match the date criteria.

Hope this helps

it still doens't work..

jon_k can i send to you my file?help please!:(

ha yeah my brackets are all messed up in that example. Here is the correct formula:

=SUMPRODUCT(--($A1:$A$1000="DSSI"),--($B$1:$B$1000<=DATEVALUE("31/01/09")))+ SUMPRODUCT(--($A1:$A$1000="DSSL"),--($B$1:$B$1000<=DATEVALUE("31/01/09")))I've dropped it over two lines to save space, but it is one formula.

HTH

Ask Your Question

Weekly Poll

Would you ride in a self-driving car from Tesla?

Discuss in The Lounge

Poll History