Sum Product formulae

D

d_kight

I am using a sum product formula to count Column E, a date of 2/6/2008 and
column P "AUTO" and I am getting a return of 0.

EXAMPLE: the DATE is column B and AUTO is in column P. Any help would be
greatly appreciated.


Christina L 2/27/2008 Fssa TZ INWB B CR $ 50 Restart voice
Christina L 2/27/2008 Fssa SZ INWB B CR $ 15 Restart voice
RICHARD 2/6/2008 TZ INAD A $ 15 AUTO
DONNA 2/6/2008 TZ INAG A $ 15 AUTO
DAVID 2/6/2008 TZ INAH A $ 15 AUTO
BOBBY 2/6/2008 TZ INAH A $ 15 AUTO
 
J

JLatham

I suspect the problem is with the data in column B - the dates. You'll need
to either set up a cell to enter a date to match into or pick one of the
dates from column B as part of your formula. I went with the second option
(actually the poorer choice - I just did it for speed of design).
Assuming your data rows are 2 through 7, then this formula worked for me:
=SUMPRODUCT(--(B2:B7=B4),--(P2:p7="AUTO"),--(E2:E7<>""))
could also be written as
=SUMPRODUCT((B2:B7=B4)*(P2:p7="AUTO")*(E2:E7<>""))
but I believe the first way is faster, which would be a factor if you have a
lot of these.

If you set up a separate cell to hold the date to match, make sure it is
formatted exactly the same as the cells in column B holding the dates now.
If they are text, then the holding cell should be formatted as text; if
they're actually formatted as dates, then the holding cell should also be
formatted as a Date type.

Hope this helps you with the problem.
 
D

d_kight

thx Latham;; so are you saying the dates format is throwing the count askew??
and I need to set the date as an actual text?
 
J

JLatham

I'm not saying that. I suspect you have other uses for the date field, and
you probably want them as dates. What I'm saying is that whatever you put in
to match the date needs to be what Excel expects. The easiest way to do that
is to either pick a date from the group to be examined in column B, or to set
up another cell to enter the date into that is formatted the same way cells
in column B is and reference it in that part of the SUMPRODUCT() formula.

The second way is probably most efficient in the long run: you don't have to
modify your formula at all, ever. Just change the date in the input cell.
I'd actually have two "input" cells: one to enter the date and one to enter
the word/phrase from over in column P. That way your formula never needs to
change, just the entries in the two input cells.

Lets say you have columns X, Y and Z available to you. You could set up X1
in the same format as column B is set to. X1 then becomes the input cell for
the target date. Y1 could be the input cell for the text to match in column
P. Then in Z1 you could have this formula:
=SUMPRODUCT(--(B2:B7=$X$1),--(P2:p7=$Y$1),--(E2:E7<>""))
 
D

d_kight

JLatham.
Thanks for all the help, I really appreciate it. Here is how I solved the
delima.
Instead of using the actual date (ie, 2/15/2008), I used the Microsoft
equivalent number corresponding to that day (ie, 39493 - that many days past
and including Jan 1, 1900); Thus Yielding this type of formula:
=SUMPRODUCT((B2:B7 = 39493)*(P2:p7 = "AUTO'))
It seems to work like a charm.

Thanks for all the help.
 
Top