how to sum up based on a cell's value

[zjianguk]

Hi, I want to do sum-up for the 2nd row with cells: A2, B2, C2, D2, E2, F2, G2, H2, based on the value of cell J2:

If J1="Odd", then sum up A2, C2, E2 and G2
If J1="Even", then sum up B2, D2, F2, and H2.

How can I make this with a formula?


If I want to do sum-up for column B with cells: B11, B12, B13, B14, B15, B16, B17, B18:

If J1="Odd", then sum up B11, B13, B15, B17
If J1="Even", then sum up B12, B14, B16, B18

How can I do this?

The example is attached here. Thanks for your help.

[Pete_UK]

You can do this:

=IF(MOD(J1,2)=1,SUM(A2,C2,E2,G2),SUM(B2,D2,F2,H2))

and for your second query:

=IF(MOD(J1,2)=1,SUM(B11,B13,B15,B17),SUM(B12,B14,B16,B18))

Hope this helps.

Pete

[Kevin UK]

Hi zjianguk

In K1:

Please Login or Register  to view this content.

Array formula: CTRL + SHIFT + ENTER

[Tony Valko]

One way...

Assuming J2 will always contain either Even or Odd.

=SUMPRODUCT(--(MOD(COLUMN(A2:H2),2)=--(J2="odd")),A2:H2)

[bebo021999]

And another way:
=SUMPRODUCT(VLOOKUP(A2,A2:H2,IF(J2="ODD",{1,3,5,7},{2,4,6,8})))

[Pete_UK]

It seems that J1 contains the words "Odd" or "Even" rather than containing a number, so you can do this:

=IF(J1="Odd",SUM(A2,C2,E2,G2),SUM(B2,D2,F2,H2))

or:

=IF(J1="Odd",SUM(B11,B13,B15,B17),SUM(B12,B14,B16,B18))

Hope this helps.

Pete

[zjianguk]

Hi everyone, Pete, bebo, Tony, Kevin, thanks for all your nice solutions. I've written them down for myself.

I'm sure this will be useful to others as well

[Tony Valko]

You're welcome. We appreciate the feedback!