Nested ifs in CSE?

[Matt]

Say I have data similar to the following:

Col A Col B
1 3
2 6
3 3
4 4
5 1

I would like to use a CSE formula to take the sum of only the #s in
Col B that correspond to #s between 2 and 4 in Col A.

I tried this with no success:

{=sum(if(A1:A5>=2,if(A1:A5<=4,B1:B5,0),0))

Any ideas?

 

[Guest]

No array formula (CSE) needed for your situation

Try on of these:
=SUMPRODUCT((A2:A6>=2)*(A2:A6<=4)*B2:B6)
or
=SUMPRODUCT(--(A2:A6>=2),--(A2:A6<=4),B2:B6)

Adjust range references to suit your situation.

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Ragdyer]

Another way, also *not* an array formula:

=SUMIF(A1:A5,">=2",B1:B5)-SUMIF(A1:A5,">4",B1:B5)

 

[Matt]

No array formula (CSE) needed for your situation

Try on of these:
=SUMPRODUCT((A2:A6>=2)*(A2:A6<=4)*B2:B6)
or
=SUMPRODUCT(--(A2:A6>=2),--(A2:A6<=4),B2:B6)

Adjust range references to suit your situation.

Does that help?
***********
Regards,
Ron

XL2002, WinXP








- Show quoted text -

This worked perfectly, though I don't understand the syntax. Can you
explain?

 

[Guest]

Hi, Matt

Re: =SUMPRODUCT((A1:A5>=2)*(A1:A5<=4)*B1:B5)
and using your posted data
Col A Col B
1 3
2 6
3 3
4 4
5 1

Here's the explanation, by section:

(A1:A5>=2) returns a 5-element array of TRUE/FALSE values.
TRUE if a cell is greater than or equal to 2, FALSE if it is not.
{FALSE, TRUE, TRUE, TRUE, TRUE}

(A1:A5<=4) also returns a 5-element array of TRUE/FALSE values.
TRUE if a cell is less than or equal to 4, FALSE if it is not.
{TRUE, TRUE, TRUE, TRUE, FALSE}

and B1:B5 simply returns the values in those cells.

Note...when boolean values (TRUE/FALSE) are used in an arithmetic operation,
TRUE becomes 1 and FALSE becomes zero.

So {FALSE, TRUE, TRUE, TRUE, TRUE}*{TRUE, TRUE, TRUE, TRUE, FALSE}*{3,6,3,4,1}
effectively becomes {0,1,1,1,1}*{1,1,1,1,0}*{3,6,3,4,1}
which reduces this way
{0x1x3,1x1x6,1x1x3,1x1x4,1x0x1}
or
{0,6,3,4,0}

and the SUMPRODUCT function add up those values, returning: 13

I hope that helps.

Post back with more questions.

***********
Regards,
Ron

XL2002, WinXP