Tom's Guide | Tom's Hardware | Tom's Games

Computing.Net > Forums > Programming > Countif function with 3 conditions

Computer Problems? Computing.Net has over 1,000,000 posts about all things technology related! Over 90% answered within 24 hours! Click here to start participating now! Also, be sure to check out the New User Guide.

Countif function with 3 conditions

Reply to Message Icon

Name: ice
Date: February 2, 2009 at 01:34:11 Pacific
OS: Windows XP
Subcategory: General
Comment:

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!!



Sponsored Link
Ads by Google

Response Number 1
Name: Razor2.3
Date: February 2, 2009 at 08:23:18 Pacific
Reply:

Just add them together.

=COUNTIF(A:B, "<=January 31, 2009") +COUNTIF(A:B, "=DSSL")+COUNTIF(A:B, "=DSSI")


0

Response Number 2
Name: ice
Date: February 2, 2009 at 22:23:57 Pacific
Reply:

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 :(


0

Response Number 3
Name: jon_k
Date: February 3, 2009 at 07:12:36 Pacific
Reply:

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


0

Response Number 4
Name: ice
Date: February 3, 2009 at 20:49:00 Pacific
Reply:

it still doens't work..
jon_k can i send to you my file?

help please!:(


0

Response Number 5
Name: jon_k
Date: February 4, 2009 at 01:10:28 Pacific
Reply:

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


0

Related Posts

See More



Sponsored Link
Ads by Google
Reply to Message Icon



Post Locked

This post is quite old and has been locked from receiving new replies. Please create a new posting instead.


Go to Programming Forum Home


Sponsored links
Ads by Google


Results for: Countif function with 3 conditions
project logic help needed www.computing.net/answers/programming/project-logic-help-needed/8163.html

Big development decisions www.computing.net/answers/programming/big-development-decisions/19722.html

C++..HELP me with character array www.computing.net/answers/programming/chelp-me-with-character-array/10148.html