Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to formulate this???
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to formulate this???
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; "formula" wrote in message : 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to formulate this???
"formula" wrote:
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". |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to formulate this???
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 " wrote: "formula" wrote: 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". |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to formulate this???
formula wrote:
" wrote: "formula" wrote: 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". 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do i formulate to add certain colored numbers? | Excel Discussion (Misc queries) | |||
time formulate | Excel Discussion (Misc queries) | |||
How do you formulate an entire column? | Excel Worksheet Functions | |||
HOW DO I FORMULATE A CELL TO ROUND $5.82 TO $5.85 | Excel Discussion (Misc queries) | |||
Excel: how to formulate conditional cell references | Excel Worksheet Functions |