Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 430
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 418
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 418
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do i formulate to add certain colored numbers? Jawz22 Excel Discussion (Misc queries) 2 July 30th 06 06:16 PM
time formulate arslan Excel Discussion (Misc queries) 3 April 16th 06 01:22 PM
How do you formulate an entire column? SWcoord Excel Worksheet Functions 1 February 15th 06 06:39 PM
HOW DO I FORMULATE A CELL TO ROUND $5.82 TO $5.85 rikg Excel Discussion (Misc queries) 3 April 9th 05 01:53 PM
Excel: how to formulate conditional cell references centraloffice Excel Worksheet Functions 1 December 10th 04 08:57 PM


All times are GMT +1. The time now is 08:38 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"