Sumproduct & Dates

[Chuck]

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

 

[Peo Sjoblom]

Your formula doesn't make any sense to me, if you have the dates and the
values and you want to see how much was spend for a certain month

=SUMPRODUCT(--(ISNUMBER('Purchase Record'!$G$6:$G$18)),--(MONTH('Purchase
Record'!$G$6:$G$18)=1),'Purchase Record'!$F$6:$F$18)

will return what was spent for January, and if you have index numbers for
the months in C6:N6 and C6 holds 1 then you can replace 1 with C6 and copy
across

INT is used to strip out days from times but it is not necessary when you
use the MONTH function. Also you need to have the same size for the arrays
you are testing or else you will get num errors

 

[Chuck]

hey Peo,

just wanted to say thanks for the correct function. works flawlessly.
also, had to amend my date cells to show 0 when no date is found

cheers
chuck