Multiple dynamic ranges in a Sumproduct

[mmartens12 via OfficeKB.com]

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!

 

[Biff]

Hi!

You should base all range sizes on the "key" column. For example:

.............A............B.............C
1..........X...........10............20
2..........X...........................10
3..........X...........50................
4..........X...............................

The "key" column has an entry in every cell and defines the vertical size of
the entire table.

So, if you used a dynamic range for each of those columns:

A = Rng1
B = Rng2
C = Rng3

Rng1 =OFFSET($A$1,,,COUNTA($A:$A))
Rng2 =OFFSET($B$1,,,COUNTA($A:$A))
Rng3 =OFFSET($C$1,,,COUNTA($A:$A))

If the "key" column might contain blank or empty cells it can get really
complicated!

Biff

 

[mmartens12 via OfficeKB.com]

Brilliant!!!! Thank you! It works great!

 

[Biff]

You're welcome. Thanks for the feedback!

Biff