Bit of a complex SUM
 

Hi,

I've got a large worksheet doing a variety of calculations. One section of
the data looks like:
A .... F G H
6 one four
0 one
25 three three
9 two two
4 one
0

Column A always contains a whole number or 0.
Column F contains one of a set of text values defined in column G. If Column
A is zero, Column F will be blank.
Column G is a list of possible values of Column F, sorted in alphabetical
order.
I have no way of knowing what values are in Column A, nor how many rows are
used (though I can set a high limit and unused rows will contain zero).
I have no way of knowing what value is in column F other than the limits
described above.

What I need to do is SUM the values in Column A according to the value in
Column F, so columns G and H would look like:
G H
four 0
one 10
three 25
two 9

Help on the formula to do this would be much appreciated.

This is in XL 2003 if it makes any difference.

TIA

Dave

Bit of a complex SUM
 

In H1, use this formula and copy down as needed.

=SUMIF(F:F,G1,A:A)

HTH,
Paul

--

"Risky Dave" wrote in message
...
Hi,

I've got a large worksheet doing a variety of calculations. One section of
the data looks like:
A .... F G H
6 one four
0 one
25 three three
9 two two
4 one
0

Column A always contains a whole number or 0.
Column F contains one of a set of text values defined in column G. If
Column
A is zero, Column F will be blank.
Column G is a list of possible values of Column F, sorted in alphabetical
order.
I have no way of knowing what values are in Column A, nor how many rows
are
used (though I can set a high limit and unused rows will contain zero).
I have no way of knowing what value is in column F other than the limits
described above.

What I need to do is SUM the values in Column A according to the value in
Column F, so columns G and H would look like:
G H
four 0
one 10
three 25
two 9

Help on the formula to do this would be much appreciated.

This is in XL 2003 if it makes any difference.

TIA

Dave

Bit of a complex SUM
 

Put this in column H next to "four" (assume this is H1):

=SUMIF(F:F,G1,A:A)

Copy down to H4.

Hope this helps.

Pete

On Mar 18, 1:52*pm, Risky Dave
wrote:
Hi,

I've got a large worksheet doing a variety of calculations. One section of
the data looks like:
A * * .... * *F * * * *G * * * * * *H * * * *
6 * * * * * * one * * four * *
0 * * * * * * * * * * * one * *
25 * * * * * three * three
9 * * * * * * two * * two
4 * * * * * * one
0 * * * * * * *

Column A always contains a whole number or 0.
Column F contains one of a set of text values defined in column G. If Column
A is zero, Column F will be blank.
Column G is a list of possible values of Column F, sorted in alphabetical
order.
I have no way of knowing what values are in Column A, nor how many rows are
used (though I can set a high limit and unused rows will contain zero).
I have no way of knowing what value is in column F other than the limits
described above.

What I need to do is SUM the values in Column A according to the value in
Column F, so columns G and H would look like:
G * * * *H
four * *0
one * *10
three *25
two * *9

Help on the formula to do this would be much appreciated.

This is in XL 2003 if it makes any difference.

TIA

Dave

Bit of a complex SUM
 

Gents,

My thanks. Brain is obviously not in gear today:-)

Dave

"Pete_UK" wrote:

Put this in column H next to "four" (assume this is H1):

=SUMIF(F:F,G1,A:A)

Copy down to H4.

Hope this helps.

Pete

On Mar 18, 1:52 pm, Risky Dave
wrote:
Hi,

I've got a large worksheet doing a variety of calculations. One section of
the data looks like:
A .... F G H
6 one four
0 one
25 three three
9 two two
4 one
0

Column A always contains a whole number or 0.
Column F contains one of a set of text values defined in column G. If Column
A is zero, Column F will be blank.
Column G is a list of possible values of Column F, sorted in alphabetical
order.
I have no way of knowing what values are in Column A, nor how many rows are
used (though I can set a high limit and unused rows will contain zero).
I have no way of knowing what value is in column F other than the limits
described above.

What I need to do is SUM the values in Column A according to the value in
Column F, so columns G and H would look like:
G H
four 0
one 10
three 25
two 9

Help on the formula to do this would be much appreciated.

This is in XL 2003 if it makes any difference.

TIA

Dave

Bit of a complex SUM
 

Glad to be of help, Dave.

Pete

On Mar 18, 2:24*pm, Risky Dave
wrote:
Gents,

My thanks. Brain is obviously not in gear today:-)

Dave



"Pete_UK" wrote:
Put this in column H next to "four" (assume this is H1):

=SUMIF(F:F,G1,A:A)

Copy down to H4.

Hope this helps.

Pete

On Mar 18, 1:52 pm, Risky Dave
wrote:
Hi,

I've got a large worksheet doing a variety of calculations. One section of
the data looks like:
A * * .... * *F * * * *G * * * * * *H * * * *
6 * * * * * * one * * four * *
0 * * * * * * * * * * * one * *
25 * * * * * three * three
9 * * * * * * two * * two
4 * * * * * * one
0 * * * * * * *

Column A always contains a whole number or 0.
Column F contains one of a set of text values defined in column G. If Column
A is zero, Column F will be blank.
Column G is a list of possible values of Column F, sorted in alphabetical
order.
I have no way of knowing what values are in Column A, nor how many rows are
used (though I can set a high limit and unused rows will contain zero)..
I have no way of knowing what value is in column F other than the limits
described above.

What I need to do is SUM the values in Column A according to the value in
Column F, so columns G and H would look like:
G * * * *H
four * *0
one * *10
three *25
two * *9

Help on the formula to do this would be much appreciated.

This is in XL 2003 if it makes any difference.

TIA

Dave- Hide quoted text -

- Show quoted text -