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

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

=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



.

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

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