Trouble With Conditional Counting

S

southbym

Here's my example data:

Date State Person Car Truck Bike Boat

Mon FLA John Y Y Y
Mon TEX Mary Y
Tue FLA John Y Y
Tue FLA Pete Y
Wed CA John Y Y
Thu FLA John Y Y
Fri FLA Mary Y
Fri FLA John Y Y

What I want to is calculate how many vehicles John sold in Florida
the answer should be 9 (3 + 2 + 2 + 2).

I'm guessing that the formula needs to check State and Person for "FLA
and "John", then do a COUNTA across the row of vehicles.

Regards,

Mar
 
B

Biff

Hi Mark!

Try this array entered formula: CTRL,SHIFT,ENTER

=SUM(IF(B3:B10="FLA",IF(C3:C10="JOHN",IF(D3:G10="Y",1))))

Biff
-----Original Message-----
Here's my example data:

Date State Person Car Truck Bike Boat

Mon FLA John Y Y Y
Mon TEX Mary Y
Tue FLA John Y Y
Tue FLA
Click to expand...
Pete Y
 
J

Jack Schitt

You may want to enter the actual number of each item sold instead of "Y", ie
if a particular individual might sell several bikes in one day in Florida.
Then
=SUM((B2:B9="FLA")*(C2:C9="JOHN")*D2:G9)
array entered (Control/Shift/Enter)
But if you need to use "Y" then
=SUM((B2:B9="FLA")*(C2:C9="JOHN")*(D2:G9="Y"))
array entered
 
D

Dominik

Or use SUMPRODUCT instead of SUM and you don't have to
array-enter.

HTH,
Dominik.
 
B

Biff

Hi!

How would you use sumproduct? The last condition, =Y, has
a different sized and shaped array. Of course, you could
string a bunch of sumproducts together for each individual
condition but then you end up with an ugly monster formula!

Biff
 
J

Jack Schitt

Hi Biff

It worked for me. I just substituted SUMPRODUCT for SUM, standard entered,
and it worked. Dominik is right, it seems.
 
Top