Sum multiple criteria with a range than and < than
 

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$46890,data!$G$2:$G$468<121))*(data!$H$2:$H$468 ))

TIA

Sum multiple criteria with a range than and < than
 

=SUMPRODUCT((data!$A$2:$A$468=$A$6)*(data!$G$2:$G$ 46890)*(data!$G$2:$G$468<121)*(data!$H$2:$H$468))
--
David Biddulph


"Jan" wrote in message
...
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$46890,data!$G$2:$G$468<121))*(data!$H$2:$H$468 ))

TIA

Sum multiple criteria with a range than and < than
 

Try it like this:

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


--
Biff
Microsoft Excel MVP


"Jan" wrote in message
...
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$46890,data!$G$2:$G$468<121))*(data!$H$2:$H$468 ))

TIA

Sum multiple criteria with a range than and < than
 

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
---------------
Jacob Skaria

Sum multiple criteria with a range than and < than
 

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

"T. Valko" wrote:

Try it like this:

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


--
Biff
Microsoft Excel MVP


"Jan" wrote in message
...
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$46890,data!$G$2:$G$468<121))*(data!$H$2:$H$468 ))

TIA

Sum multiple criteria with a range than and < than
 

=SUMPRODUCT(--(data!$A$2:$A$468=$A$6),--(data!$G$2:$G$46890),--(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$46890)
(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$46890)
--(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

--
Biff
Microsoft Excel MVP


"Jan" wrote in message
...
Formula worked. Thank you.
I'm curious. What do the dashes mean?

"T. Valko" wrote:

Try it like this:

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


--
Biff
Microsoft Excel MVP


"Jan" wrote in message
...
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$46890,data!$G$2:$G$468<121))*(data!$H$2:$H$468 ))

TIA