Sumproduct has me stumped

[chrysti]

I want to use a sumproduct, but am open to another formula that will do the same thing, that will do:
sumproduct(--($A$2:$A$20=$A$1),--($B$2:$B$20=$B$1),($C$2:$C$20)

Here is the catch...my area of $2:$20 changes on a weekly basis, sometimes it goes to row 18 sometimes to 12 sometimes to 20...I am trying to write the formula so that it will change as my range changes it can refer to a cell that counts the number of cells filled in if needed, that I have right now already (that is how I am changing my formula each week).

Thanks in advance for the help!
C

 

[Xtrimmer]

I would just make your ranges, say A2:A20, larger than your list will ever be.

For example:

=SUMPRODUCT(--($A$2:$A$200=A1),--($B$2:$B$200=B1),$C$2:$C$200)

So, no matter if you have 2 or 200 lines to calculate, it will work. It will just add zero to the total for every blank row.

 

[chrysti]

So that is what I have now, but this is in many columns on many tabs and it takes like an hour to calculate so I am looking for a way to shorten this up as much as I can.

Thanks
C

 

[jeffreybrown]

What version of Excel are you using?

 

[Aladin Akyurek]

I want to use a sumproduct, but am open to another formula that will do the same thing, that will do:
sumproduct(--($A$2:$A$20=$A$1),--($B$2:$B$20=$B$1),($C$2:$C$20)

Here is the catch...my area of $2:$20 changes on a weekly basis, sometimes it goes to row 18 sometimes to 12 sometimes to 20...I am trying to write the formula so that it will change as my range changes it can refer to a cell that counts the number of cells filled in if needed, that I have right now already (that is how I am changing my formula each week).

Thanks in advance for the help!
C

Some options...

1) If you are on 2007 or later, you might want to switch to:

=SUMIFS($C$2:$C$20,$A$2:$A$20,$A$1,$B$2:$B$20,$B$1)

2) Concatenate the ranges of interest such that the formulas can be set up with lesser number of conditions, even just one.

D2, just enter and copy down:

=A2&"|"&B2

Then invoke:

=SUMIF($D$2:$D$20,$A$1&"|"&$B$1,$C$2:$C$20)

3) You might want to research whether you can switch to formulas with DSUM.

4) Working with dynamic ranges here does not promise a significant gain for it only involves a few rows and the ranges are not big.

 

[chrysti]

I was hoping to be able to do something like:
sumproduct(--(REFERNECE CELL WITH RANGE IN IT=$A$1),--(REFERENCE CELL WITH RANGE IN IT=$B$1),(REFERENCE CELL WITH RANGE IN IT)

That way in the cell I can have it say $A$2:$A$20 or whatever it is that week and then $B$2:$B$20 or whatever it is that week and then sum this range $C$2:$C$20 or whatever it is that week.

Possible???

Thanks
C

 

[Aladin Akyurek]

I was hoping to be able to do something like:
sumproduct(--(REFERNECE CELL WITH RANGE IN IT=$A$1),--(REFERENCE CELL WITH RANGE IN IT=$B$1),(REFERENCE CELL WITH RANGE IN IT)

That way in the cell I can have it say $A$2:$A$20 or whatever it is that week and then $B$2:$B$20 or whatever it is that week and then sum this range $C$2:$C$20 or whatever it is that week.

Possible???

Thanks
C

That would aggravate your performance problem for that requires using INDIRECT, a volatile function.