Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct & dates
hi all,
i have created (or designed) an excel doc that tracks purchasing for my projects. what i am trying to do is track how much is spent per month in one tab, i have all purchases consolidated .. which cotain the amount of a PO and the date of which was purchased Purchase Record tab Column = PO Value (range F6:F18) Column = Date Purchased (range G6:G18) on a next tab, i am trying to identify how much total spend there was per month using the tab above Spend Calendar tab (where the issue is) i have a start date .. say 01/07/2007 (B5) Row = month number (range C6:N6) eg, 1-12 (for the months of the year) couple of rows down is where i am trying to use SUMPRODUCT.. =SUMPRODUCT((INT(MONTH('Purchase Record'!$G$6:$G $18)=INT(C7))*('Purchase Record'!$F$6:$F$7))) i recieve an #N/A when there is no data within the Purchase record cell's. is there a way to ignore all empty rows and move on to the next so it can calculate? i have put the worksheet on a website so you guys can see what i am talking about http://dev1.replayfunctionsband.net...._Template1.zip Cheers |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct & dates
You have a couple of problems with this.
In Purchase Record column G you have a formula that returns either a date or a formula blank. The MONTH function is causing an error when trying to evaluate these formula blanks. Also, this formula: =SUMPRODUCT((INT(MONTH('Purchase Record'!$G$6:$G$18)=INT(C7))*('Purchase Record'!$F$6:$F$7))) is using uneven sized ranges. 'Purchase Record'!$F$6:$F$7 must be the same size as 'Purchase Record'!$G$6:$G$18. You don't need the INT functions. Change your formulas in column G of Purchase Record to return 0 instead of the blank. If you don't want to see the 0s use conditional formatting to hide them. Then you can write your formula like this: =SUMPRODUCT(--('Purchase Record'!$G$6:$G$180),--(MONTH('Purchase Record'!$G$6:$G$18)=C7),'Purchase Record'!$F$6:$F$18) -- Biff Microsoft Excel MVP wrote in message oups.com... hi all, i have created (or designed) an excel doc that tracks purchasing for my projects. what i am trying to do is track how much is spent per month in one tab, i have all purchases consolidated .. which cotain the amount of a PO and the date of which was purchased Purchase Record tab Column = PO Value (range F6:F18) Column = Date Purchased (range G6:G18) on a next tab, i am trying to identify how much total spend there was per month using the tab above Spend Calendar tab (where the issue is) i have a start date .. say 01/07/2007 (B5) Row = month number (range C6:N6) eg, 1-12 (for the months of the year) couple of rows down is where i am trying to use SUMPRODUCT.. =SUMPRODUCT((INT(MONTH('Purchase Record'!$G$6:$G $18)=INT(C7))*('Purchase Record'!$F$6:$F$7))) i recieve an #N/A when there is no data within the Purchase record cell's. is there a way to ignore all empty rows and move on to the next so it can calculate? i have put the worksheet on a website so you guys can see what i am talking about http://dev1.replayfunctionsband.net...._Template1.zip Cheers |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct & dates
hi biff,
just wanted to say that your my hero ... it works like a charm cheers for that |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct & dates
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Chuck" wrote in message oups.com... hi biff, just wanted to say that your my hero ... it works like a charm cheers for that |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT - DIFFERENT DATES | Excel Worksheet Functions | |||
Sumproduct and dates | New Users to Excel | |||
SUMPRODUCT and Dates | Excel Worksheet Functions | |||
sumproduct with dates | Excel Worksheet Functions | |||
sumproduct between two dates | Excel Worksheet Functions |