Solved: sumproduct question 2

[vzachin]

hi,

in an earlier post today, http://www.vbaexpress.com/forum/showthread.php?t=40650
how can i modify the formula to add in new rows of data?

eg: =SUMPRODUCT(--((MONTH($A$6:$A$15)=ROW(I1))*($B$6:$B$15=L$5)))

thanks
za

[p45cal]

I haven't looked at your spreadsheet file, but it's usually a qustion of relative/absolute references, so it might be something like:
=SUMPRODUCT(--((MONTH($A$6:$A15)=ROW(I1))*($B$6:$B15=L$5)))

[vzachin]

hi P45cal,

i tried as you suggested but it does not work for me.

column a contains dates and column b contains widgets (a,b,c).

the sumproduct formula gives me my widgets vs months in 3 different columns.

as i add rows for my dates and widgets, the sumproduct formula needs take into account my new rows.
i can use
=SUMPRODUCT(--((MONTH($A$6:$A$65536)=ROW(I1))*($B$6:$B$65536=L$5)))
but that takes a while to re-calculate.

thanks for looking at this
za

[p45cal]

from the sheet given in the other thread, try in K6:
=SUMPRODUCT(--((MONTH(TheDates)=ROW(D1))*(OFFSET(TheDates,0,1)=K$5)),OFFSET(TheDates,0,2) )
Copy down and across.
Needs a defined name TheDates defined as:
=OFFSET(Sheet1!$A$6,0,0,COUNT(Sheet1!$A$6:$A$65536),1)
Assumes the dates are contiguous in column A and always start in A6.

[vzachin]

hi P45cal,

works and is much quicker now.
GREAT! thanks a lot!

za