SUMPRODUCT when you want to use all rows in column

  • Thread starter Thread starter Android
  • Start date Start date
A

Android

Hello,

I'm using something like:
=SUMPRODUCT(('DataSheet'!$A:$A$=ThisSheet!E17)*('DataSheet'!$D:$D=ThisSheet!
$G$7)*'DataSheet'!$I:$I)

to sum all rows in the "I" column of "DataSheet", if two conditions in
"ThisSheet" are met (as indicated in the expression).

However, in my "DataSheet" sheet, the 1st row is labels, which I want to
keep. Therefore the above expression gives an error. Using
'DataSheet'!$A2:$A$ gives an error.

Is there a way to apecify all rows except the first one? The number of rows
grows over time, so I do not want to specify a limit.

Any options??

Regards...
 
All rows bar the first would be A2:A65536 - All your ranges need to be the same
size here though, so D2:D65536 etc

You could also use a dynamic range to set the range using the height argument of
OFFSET() for example.
 
Hi
you may define a name for your used range. Note: You can't use a
reference like A:A within SUMPRODUCT.
Try the following name Definition:
name_1 = OFFSET('DataSheet'!$A$2,0,0,COUNTA('DataSheet'!$A$2:$A$20000))
name_2 = OFFSET('DataSheet'!$D$2,0,0,COUNTA('DataSheet'!$A$2:$A$20000))
name_3 = OFFSET('DataSheet'!$I$2,0,0,COUNTA('DataSheet'!$A$2:$A$20000))


now use these names in SUMPRODCT:
=SUMPRODUCT((name_1=ThisSheet!E17)*(name_2=ThisSheet!$G$7),name_3)
 
Thank you for description for using OFFSET and Names. Will try it out.

Regards,

Android.
 
Thought I'd post the solution I finally used, which is a variation on your
suggestion.

To avoid using a hardcoded max row number, I used:

name_1 = OFFSET('DataSheet'!$A$2,0,0,COUNTA('DataSheet'!$A:$A)-1)

It appears to work.

If I understand OFFSET correctly, this selects rows 2 and counts down &
selects max # of rows (which includes row 1) minus 1.

Regards,

Android.
 
Thought I'd post the solution I finally used, which is a variation on your
suggestion.

To avoid using a hardcoded max row number, I used:

name_1 = OFFSET('DataSheet'!$A$2,0,0,COUNTA('DataSheet'!$A:$A)-1)

It appears to work.
Click to expand...
...

Note that it only works when there are no blank cells in column A. If, e.g., you
had data in cells A1:A20 except for A5 and A12, which were blank, then

=OFFSET($A$2,0,0,COUNTA($A:$A)-1)

would resolve to A2:A18 rather than A2:A20. The robust approach is the array
formula

=OFFSET($A$2,0,0,MAX((1-ISBLANK($A$2:$A$65536))*ROW($A$2:$A$65536))-1,1)
 
Good point.

So it looks like the hardcoded number (65536) is needed after all.

Thanks,

Android.
 
Back
Top