sumproduct & dates

[cvanoosbree]

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.au/Project_Purchasing_Template1.zip

Cheers

 

[T. Valko]

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$18>0),--(MONTH('Purchase
Record'!$G$6:$G$18)=C7),'Purchase Record'!$F$6:$F$18)

 

[Chuck]

hi biff,

just wanted to say that your my hero ... it works like a charm

cheers for that

 

[T. Valko]

You're welcome. Thanks for the feedback!