Countif function with 3 conditions

ice February 2, 2009 at 01:34:11
Specs: Windows XP
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!!


See More: Countif function with 3 conditions

Report •


#1
February 2, 2009 at 08:23:18
Just add them together.
=COUNTIF(A:B, "<=January 31, 2009") +COUNTIF(A:B, "=DSSL")+COUNTIF(A:B, "=DSSI")


Report •

#2
February 2, 2009 at 22:23:57
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 :(


Report •

#3
February 3, 2009 at 07:12:36
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


Report •

Related Solutions

#4
February 3, 2009 at 20:49:00
it still doens't work..
jon_k can i send to you my file?

help please!:(


Report •

#5
February 4, 2009 at 01:10:28
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


Report •


Ask Question