Nested ifs in CSE?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Matt - 09 Apr 2007 20:15 GMT

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?

Reply to this Message

Ron Coderre - 09 Apr 2007 20:34 GMT

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

> Say I have data similar to the following:
>
[quoted text clipped - 13 lines]
>
> Any ideas?

Reply to this Message

Matt - 10 Apr 2007 21:29 GMT

On Apr 9, 2:34 pm, Ron Coderre <RonCode...@discussions.microsoft.com>
wrote:

> No array formula (CSE) needed for your situation
>
[quoted text clipped - 31 lines]
>
> - Show quoted text -

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

Reply to this Message

Ron Coderre - 10 Apr 2007 23:44 GMT

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

> On Apr 9, 2:34 pm, Ron Coderre <RonCode...@discussions.microsoft.com>
> wrote:
[quoted text clipped - 36 lines]
> This worked perfectly, though I don't understand the syntax. Can you
> explain?

Reply to this Message

Ragdyer - 09 Apr 2007 22:11 GMT

Another way, also *not* an array formula:

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

Signature

HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

> Say I have data similar to the following:
>
[quoted text clipped - 13 lines]
>
> Any ideas?

Reply to this Message

Rate this thread: