Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct using date
I am trying desperately to get sumproduct to work. I need to get sum of
hours before a certain date and hours after a certain date. Here's what I'm trying to do: IF Sheet2 A2:A50 = Sheet1 A2 (customer name match) IF Sheet2 C2:C50 <= Sheet1 C4 (this is date, i.e. 03-2006) Then sum up Sheet2 G2:G50 (this is hours worked) Sometimes the formula works, sometimes it displays a 0. Thanks in advance. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct using date
You mention 2 dates but show only one. Does 03-2006 mean March 1, 2006 or
March 31, 2006. Where is the second date? Tyro "jhicsupt" wrote in message ... I am trying desperately to get sumproduct to work. I need to get sum of hours before a certain date and hours after a certain date. Here's what I'm trying to do: IF Sheet2 A2:A50 = Sheet1 A2 (customer name match) IF Sheet2 C2:C50 <= Sheet1 C4 (this is date, i.e. 03-2006) Then sum up Sheet2 G2:G50 (this is hours worked) Sometimes the formula works, sometimes it displays a 0. Thanks in advance. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct using date
Yes, means March 1, 2006
IF Sheet2 C2:C50 <= Sheet1 C4 (this is date, i.e. 03-2006) Sheet2 C2:C50 has multiple dates. If date is <= 3/1/2006, then add hours in column G. Sheet1 C4 has 03-2006 "Tyro" wrote: You mention 2 dates but show only one. Does 03-2006 mean March 1, 2006 or March 31, 2006. Where is the second date? Tyro "jhicsupt" wrote in message ... I am trying desperately to get sumproduct to work. I need to get sum of hours before a certain date and hours after a certain date. Here's what I'm trying to do: IF Sheet2 A2:A50 = Sheet1 A2 (customer name match) IF Sheet2 C2:C50 <= Sheet1 C4 (this is date, i.e. 03-2006) Then sum up Sheet2 G2:G50 (this is hours worked) Sometimes the formula works, sometimes it displays a 0. Thanks in advance. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct using date
Try this
=SUMPRODUCT((Sheet2!A2:A50=Sheet1!A2)*(Sheet2!C2:C 50<=Sheet1!C4)*Sheet2!G2:G50) Tyro "jhicsupt" wrote in message ... Yes, means March 1, 2006 IF Sheet2 C2:C50 <= Sheet1 C4 (this is date, i.e. 03-2006) Sheet2 C2:C50 has multiple dates. If date is <= 3/1/2006, then add hours in column G. Sheet1 C4 has 03-2006 "Tyro" wrote: You mention 2 dates but show only one. Does 03-2006 mean March 1, 2006 or March 31, 2006. Where is the second date? Tyro "jhicsupt" wrote in message ... I am trying desperately to get sumproduct to work. I need to get sum of hours before a certain date and hours after a certain date. Here's what I'm trying to do: IF Sheet2 A2:A50 = Sheet1 A2 (customer name match) IF Sheet2 C2:C50 <= Sheet1 C4 (this is date, i.e. 03-2006) Then sum up Sheet2 G2:G50 (this is hours worked) Sometimes the formula works, sometimes it displays a 0. Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT with Date() -60 | Excel Worksheet Functions | |||
Sumproduct by date | Excel Discussion (Misc queries) | |||
Use SUMPRODUCT with a Date | Excel Discussion (Misc queries) | |||
if, sumproduct, help by date | Excel Worksheet Functions | |||
if, sumproduct, help by date | Excel Worksheet Functions |