Conditional Sum

Using the same formula, I want to be able to sum 3 different sets of data
independently based on a pointer in a cell. For example:
A B C D E F G H
I
1
2 10 20 30
3 5 10 15
4 First 2 4 6
5

Using a formula in cell A5, based on the information in cell A4, sum the
appropriate row. In cell A4, First = row 2, Second = row 3, Third = row 4.
The above answer in cell A5 would be 10+20+30=60. If I changed A4 to
"Second", then the answer in cell A5 is 30.

Can this be done?

Assuming that D2:F4 contains your data, try...

=SUM(INDEX(D2:F4,MATCH(A4,{"First","Second","Third"},0),0))

Hope this helps!

In article <4D7027BC-D26C-4832-86A2-A1D0C044CECE@microsoft.com>,
randys <randys@discussions.microsoft.com> wrote:

> Using the same formula, I want to be able to sum 3 different sets of data
> independently based on a pointer in a cell. For example:
> A B C D E F G H
> I
> 1
> 2 10 20 30
> 3 5 10 15
> 4 First 2 4 6
> 5
>
> Using a formula in cell A5, based on the information in cell A4, sum the
> appropriate row. In cell A4, First = row 2, Second = row 3, Third = row 4.
> The above answer in cell A5 would be 10+20+30=60. If I changed A4 to
> "Second", then the answer in cell A5 is 30.
>
> Can this be done?

THANK YOU for the quick answer. Let me complicate it a bit more ... (not that
I want to, but this is closer to the problem I'm trying to solve)

Using the same formula, I want to be able to sum 3 different sets of data
independently based on a pointer in a cell. For example:
A B C D E F G
1 First <formula>
2 10 Second
3 5
4 2
5
6 Second
7 20
8 10
9 4
10
..
..
..
21 Third
22 30
23 15
24 6

Using a formula in cell G1, based on the information in cell G2, sum the
appropriate 3 values in column D.
If G2="First", the answer would be 10+5+2=17. If G2="Second", the answer
would be 20+10+4=34. If G2="Third", the answer would be 30+15+6=51.

Please help!

"Domenic" wrote:

> Assuming that D2:F4 contains your data, try...
>
> =SUM(INDEX(D2:F4,MATCH(A4,{"First","Second","Third"},0),0))
>
> Hope this helps!
>
> In article <4D7027BC-D26C-4832-86A2-A1D0C044CECE@microsoft.com>,
> randys <randys@discussions.microsoft.com> wrote:
>
> > Using the same formula, I want to be able to sum 3 different sets of data
> > independently based on a pointer in a cell. For example:
> > A B C D E F G H
> > I
> > 1
> > 2 10 20 30
> > 3 5 10 15
> > 4 First 2 4 6
> > 5
> >
> > Using a formula in cell A5, based on the information in cell A4, sum the
> > appropriate row. In cell A4, First = row 2, Second = row 3, Third = row 4.
> > The above answer in cell A5 would be 10+20+30=60. If I changed A4 to
> > "Second", then the answer in cell A5 is 30.
> >
> > Can this be done?
>

Assuming that each set contains three rows, try...

=SUM(OFFSET(D1,MATCH(G2,A:A,0),0,3))

Hope this helps!

In article <649F29D8-60B4-4982-A834-3F3F1667BE74@microsoft.com>,
randys <randys@discussions.microsoft.com> wrote:

> THANK YOU for the quick answer. Let me complicate it a bit more ... (not that
> I want to, but this is closer to the problem I'm trying to solve)
>
> Using the same formula, I want to be able to sum 3 different sets of data
> independently based on a pointer in a cell. For example:
> A B C D E F G
> 1 First <formula>
> 2 10 Second
> 3 5
> 4 2
> 5
> 6 Second
> 7 20
> 8 10
> 9 4
> 10
> .
> .
> .
> 21 Third
> 22 30
> 23 15
> 24 6
>
> Using a formula in cell G1, based on the information in cell G2, sum the
> appropriate 3 values in column D.
> If G2="First", the answer would be 10+5+2=17. If G2="Second", the answer
> would be 20+10+4=34. If G2="Third", the answer would be 30+15+6=51.
>
> Please help!