Sum if or sum product?
 

I looked at the postings for Sum if and Sum product, but can't get it to do
what I need. I want to count the total hours for Pam if she is listed for a
shift (G9:M16). The hours of the shift are in F9:F16 (see below)

I've tried =SUMPRODUCT((G9:M16="Pam"),F9:F16) & received a value error.
=SUMIF(G9:M17,"Pam",F9:F16) only results in 7.5, counting her first occurence.
F SHIFTS
7:00 AM 2:30 PM 7.5 Pam Alpha
7:15 AM 2:45 PM 7.5 Pam Gloria
8:00 AM 4:00 PM 8 Pam

Sum if or sum product?
 

This is not sophisticated but it works : add an extra column after the
columns for the shifts and enter the formula:
=if(or(C1="Pam",D1="Pam",E1="Pam");1;0)
This formula gives 1 as a result if Pam has worked in any shift and returns
0 if Pam isn't in any of the shifts. Than you can multiply the number of
hours with this result.

Hope this helps.

Hans


"Tina" wrote:

I looked at the postings for Sum if and Sum product, but can't get it to do
what I need. I want to count the total hours for Pam if she is listed for a
shift (G9:M16). The hours of the shift are in F9:F16 (see below)

I've tried =SUMPRODUCT((G9:M16="Pam"),F9:F16) & received a value error.
=SUMIF(G9:M17,"Pam",F9:F16) only results in 7.5, counting her first occurence.
F SHIFTS
7:00 AM 2:30 PM 7.5 Pam Alpha
7:15 AM 2:45 PM 7.5 Pam Gloria
8:00 AM 4:00 PM 8 Pam

Sum if or sum product?
 

Tina,

Your syntax is off a bit. Try

=SUMPRODUCT((G1:M16="Pam")*(F1:F16))

Does that help?

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=537755

Sum if or sum product?
 

Your sumproduct nearly works, just replace the , with *

=SUMPRODUCT((G9:M16="Pam")*F9:F16)

Note that if Pam appears twice in any row the value in column F for
that row will be counted twice....


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=537755

Sum if or sum product?
 

Thanks everyone for your help!

"SteveG" wrote:


Tina,

Your syntax is off a bit. Try

=SUMPRODUCT((G1:M16="Pam")*(F1:F16))

Does that help?

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=537755