Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Group Repeating Text Values in a Column? | Excel Discussion (Misc queries) | |||
Counting Rows/Columns for Copying Formulas | Excel Discussion (Misc queries) | |||
Percentages | Charts and Charting in Excel | |||
How to set a formula to count the product appear how manytime | Excel Worksheet Functions | |||
Which function(s)? | Excel Worksheet Functions |