how to formulate this???

[Guest]

below:
in cell A1, i hv G6
cell A2: P3
cell A3: G1
cell A4: G2
cell A5: P2

how can i formulate G=9 or P=5???
i hv tried the function COUNTIF, but in vain. Help!!!
so many thxxxx

 

[Jim May]

If your example stays as simple as you present it;

In some cell, say C1 enter either your G or Your P

And the formula

=SUMPRODUCT(--(LEFT($A$1:$A$5,1)=C1),--(RIGHT(A1:A5,1)))

Will give you the 9 or the 5;

 

[joeu2004]

in cell A1, i hv G6
cell A2: P3
cell A3: G1
cell A4: G2
cell A5: P2
how can i formulate G=9 or P=5???

="G=" & SUMPRODUCT( (LEFT(A1:A5)="G") * RIGHT(A1:A5,LEN(A1:A5)-1) )

Similarly for "P=5".

 

[Guest]

just one remarks,
what if i add cell A5 is blank, then A6 is still P2. now with A1:A6, then
the formula should be???

once again thxxxx

 

[joeu2004]

what if i add cell A5 is blank, then A6 is still P2. now with A1:A6, then
the formula should be???

Your question is not entirely clear to me. Obviously you would simply
change all occurrences of "A1:A5" to "A1:A6" in the formula. I wonder
if you are really asking: what can you do so that Excel will update
the ranges in the formula automatically? The answer is: you need to
move A5 down to A6 either by dragging it or by inserting a row above
A5.

On the other hand, if you are wondering if the blank cell would somehow
screw up the sum, the answer is: no. The LEFT() part of the formula
ensures that we only sum the RIGHT() part that corresponds to "G" (or
to "P" if you make the appropriate change) Study the LEFT and RIGHT
function help pages.