Multiple dynamic ranges in a Sumproduct
 

I have been keeping track of my call log in excel. Since the nature of a
call log grows, i want to put dynmaic ranges in my formulas. I have been
using SUMPRODUCT to see how many calls have been by phone from all the other
offices.

Old
=SUMPRODUCT((MONTH(Data!$A$5:$A$683)=MONTH($A25))* (YEAR(Data!$A$5:$A$683)
=YEAR($A25))*(Data!$J$5:$J$683=J$18))

So now i have defined these ranges with
=OFFSET(Data!$J$5,0,0,COUNTA(Data!$J:$J),1)

New
=SUMPRODUCT((MONTH(Dates)=MONTH($A25))*(YEAR(Dates )=YEAR($A25))*(HelpDesk=J
$18))


This new formula works fine if i only use one dynamic range. I get a N/A
error when i add the HelpDesk range.

Another problem is when i am defining my dynamic range, i click on the
formula and the range is highlighted. When i scoll down to the bottom of my
range, there is an empty blank cell that is part of this range. Is that
giving me the error?

Thank you!

--
Message posted via http://www.officekb.com

Multiple dynamic ranges in a Sumproduct
 

range, there is an empty blank cell that is part of this range. Is that
giving me the error?

If it causes the helpdesk range to be larger than the other ranges. When
using arrays in sumproduct, they must be the same size or you will get #N/A.

The only time I've seen they can be different sizes is when using a single
cell, like
Sumproduct(H1:H10*G1)


"mmartens12 via OfficeKB.com" wrote:

I have been keeping track of my call log in excel. Since the nature of a
call log grows, i want to put dynmaic ranges in my formulas. I have been
using SUMPRODUCT to see how many calls have been by phone from all the other
offices.

Old
=SUMPRODUCT((MONTH(Data!$A$5:$A$683)=MONTH($A25))* (YEAR(Data!$A$5:$A$683)
=YEAR($A25))*(Data!$J$5:$J$683=J$18))

So now i have defined these ranges with
=OFFSET(Data!$J$5,0,0,COUNTA(Data!$J:$J),1)

New
=SUMPRODUCT((MONTH(Dates)=MONTH($A25))*(YEAR(Dates )=YEAR($A25))*(HelpDesk=J
$18))


This new formula works fine if i only use one dynamic range. I get a N/A
error when i add the HelpDesk range.

Another problem is when i am defining my dynamic range, i click on the
formula and the range is highlighted. When i scoll down to the bottom of my
range, there is an empty blank cell that is part of this range. Is that
giving me the error?

Thank you!

--
Message posted via http://www.officekb.com