Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Count Based upon Multiple Conditions
I am trying to write a formula that will count the number of new clients who
received a service in a particular month. Where B contains an X value and where the sum of C:E is greater than 1. With the data sample below the result should be 2. A B C D E Client New Divorce Custody Paternity 2003 1 2 2004 X 1 2005 2006 1 3 2007 2008 X 2 |
#2
|
|||
|
|||
One way
With the sample data posted in A1:E7, Put in F2: =IF(AND(TRIM(B2)="X",SUM(C2:E2)1),"Y","") Copy down to E7 Then put in F1: =COUNTIF(F2:F7,"Y") F1 will return the desired count of 1* (*not 2 as indicated in your post, think there's a typo) Adapt to suit -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "hkslater" wrote in message ... I am trying to write a formula that will count the number of new clients who received a service in a particular month. Where B contains an X value and where the sum of C:E is greater than 1. With the data sample below the result should be 2. A B C D E Client New Divorce Custody Paternity 2003 1 2 2004 X 1 2005 2006 1 3 2007 2008 X 2 |
#3
|
|||
|
|||
=3DSUMPRODUCT((B2:B7=3D"X")*(C2:C7+D2:D7+E2:E71))
Biff -----Original Message----- One way With the sample data posted in A1:E7, Put in F2: =3DIF(AND(TRIM(B2)=3D"X",SUM(C2:E2)1),"Y","") Copy down to E7 Then put in F1: =3DCOUNTIF(F2:F7,"Y") F1 will return the desired count of 1* (*not 2 as indicated in your post, think there's a typo) Adapt to suit -- Rgds Max xl 97 --- GMT+8, 1=B0 22' N 103=B0 45' E xdemechanik <atyahoo<dotcom ---- "hkslater" wrote in=20 message ... I am trying to write a formula that will count the=20 number of new clients who received a service in a particular month. Where B=20 contains an X value and where the sum of C:E is greater than 1. With the data=20 sample below the result should be 2. A B C D E Client New Divorce Custody Paternity 2003 1 2 2004 X 1 2005 2006 1 3 2007 2008 X 2 . |
#4
|
|||
|
|||
Put in F2: =IF(AND(TRIM(B2)="X",SUM(C2:E2)1),"Y","")
Copy down to E7 Typo, sorry .. E7 should read F7 -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#5
|
|||
|
|||
"Biff" wrote
=SUMPRODUCT((B2:B7="X")*(C2:C7+D2:D7+E2:E71)) That's much neater, Biff ! Cheers -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNTIF using multiple conditions? | New Users to Excel | |||
Create a total based on multiple conditions is not giving correct. | Excel Worksheet Functions | |||
count based on two fields- need as quick as possible | Excel Worksheet Functions | |||
count based on two fields - need quickly | Excel Worksheet Functions | |||
Count rows based on multiple criteria | Excel Worksheet Functions |