Sum multiple criteria with a range >than and < than

[Jan]

Hi,

Using Excel 2003. I have 2 worksheets. One with data as list; the other I
want to use as summary.

I've used the sumproduct which works for column b title for aging <30 and
for column G for aging >120, but I need to adapt formula when the other
columns have a aging range, i.e. >90 and less than <120. I haven't been able
to figure it out. Can some one help? I've tried a few ways by even entering
Sum as an array.
Here is last example, which obviously doesn't work.

SUMPRODUCT((data!$A$2:$A$468=$A$6)*(AND(data!$G$2:$G$468>90,data!$G$2:$G$468<121))*(data!$H$2:$H$468))

TIA

 

[David Biddulph]

=SUMPRODUCT((data!$A$2:$A$468=$A$6)*(data!$G$2:$G$468>90)*(data!$G$2:$G$468<121)*(data!$H$2:$H$468))

 

[T. Valko]

Try it like this:

=SUMPRODUCT(--(data!$A$2:$A$468=$A$6),--(data!$G$2:$G$468>90),--(data!$G$2:$G$468<121),data
$H$2:$H$468)

 

[Jacob Skaria]

The below formula should work.

Sheet2 Column A contains all numbers
Sheet1 A2 = 90
Sheet1 B2 = 120

=SUMPRODUCT((Sheet2!A:A>=A2)*(Sheet2!A:A<=B2))

If you have more columns edit the range specified

If this post helps click Yes

 

[Jan]

Formula worked. Thank you.
I'm curious. What do the dashes mean?

 

[T. Valko]

=SUMPRODUCT(--(data!$A$2:$A$468=$A$6),--(data!$G$2:$G$468>90),--(data!$G$2:$G$468<121),data
$H$2:$H$468)

What do the dashes mean?

Each of these expressions returns an array of either TRUE or FALSE.

(data!$A$2:$A$468=$A$6)
(data!$G$2:$G$468>90)
(data!$G$2:$G$468<121)

The double unary "--" coerces these to either 1 for TRUE or 0 for FALSE
which SUMPRODUCT can then work with.

--(data!$A$2:$A$468=$A$6)
--(data!$G$2:$G$468>90)
--(data!$G$2:$G$468<121)

After these arrays are converted to numbers they're all multiplied together
then added up to arrive at the result. Consider this small simplified
example:

...........A..........B.........C
1........x...........5.........2
2........y...........3.........2
3........x...........4.........3

Sum C if A = "x" and B <5

=SUMPRODUCT(--(A1:A3="x"),--(B1:B3<5),C1:C3)

The result is 3

--(A1:A3="x") returns this array:

1;0;1

--(B1:B3<5) returns this array:

0;1;1

C1:C3 returns this array:

2;2;3

All these arrays are multiplied together:

1*0*2 = 0
0*1*2 = 0
1*1*3 = 3

Then the results of the multiplication are summed to arrive at the final
result:

=SUMPRODUCT({0;0;3}) = 3