Count Based upon Multiple Conditions

[Guest]

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

 

[Max]

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

 

[Biff]

=SUMPRODUCT((B2:B7="X")*(C2:C7+D27+E2:E7>1))

Biff

 

[Max]

Put in F2: =IF(AND(TRIM(B2)="X",SUM(C2:E2)>1),"Y","")

Copy down to E7

Typo, sorry .. E7 should read F7

 

[Max]

=SUMPRODUCT((B2:B7="X")*(C2:C7+D27+E2:E7>1))

That's much neater, Biff !
Cheers