Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cells total help.
Hello Folks
Please can u help! On my worksheet call GFSA, in any the the cells C8 to C29 I have a name. It could be Big Rick in C8 only, or Big Rick in C8, C12, and C20. What is require is a formula in another sheet (called Calc) that will total the value from the corresponding Q column. Eg. GFSA sheet Cell C8="Big Rick" the give me the total from Q8 but if Big Rick is in cells c8,c12, and C20 then give me the total from Q8, Q12 and Q20. Hoping you can understand this logic. Your help is and always has been very much appreciated. Thanking you in anticipation. -- Big Rick |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cells total help.
=SUMPRODUCT(--(GFSA!C8:C29=X1),GFSA!Q8:Q29)
X1="Big Rick" "Big Rick" wrote: Hello Folks Please can u help! On my worksheet call GFSA, in any the the cells C8 to C29 I have a name. It could be Big Rick in C8 only, or Big Rick in C8, C12, and C20. What is require is a formula in another sheet (called Calc) that will total the value from the corresponding Q column. Eg. GFSA sheet Cell C8="Big Rick" the give me the total from Q8 but if Big Rick is in cells c8,c12, and C20 then give me the total from Q8, Q12 and Q20. Hoping you can understand this logic. Your help is and always has been very much appreciated. Thanking you in anticipation. -- Big Rick |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cells total help.
=SUMIF(C2:C100,"Big Rick",Q2:Q100)
replace Big Rick with a cell reference and put the name in that cell instead for better usability -- Regards, Peo Sjoblom "Big Rick" wrote in message ... Hello Folks Please can u help! On my worksheet call GFSA, in any the the cells C8 to C29 I have a name. It could be Big Rick in C8 only, or Big Rick in C8, C12, and C20. What is require is a formula in another sheet (called Calc) that will total the value from the corresponding Q column. Eg. GFSA sheet Cell C8="Big Rick" the give me the total from Q8 but if Big Rick is in cells c8,c12, and C20 then give me the total from Q8, Q12 and Q20. Hoping you can understand this logic. Your help is and always has been very much appreciated. Thanking you in anticipation. -- Big Rick |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cells total help.
you can either use the sumif function or sumproduct
I made a quick spreadsheet as follows: Col A Col B Rick 1 Car 2 Apple 3 Rick 1 Car 2 Apple 3 (i.e. a1 = Rick) SumIf in cell A8, I put the value I wanted to use in my sum (e.g. Big Rick in your example above) in cell B8 I used the following formula =SUMIF($A$1:$A$6,A8,$B$1:$B$6) SumProduct in cell A9, I put the value I wanted to use in my sum (e.g. Big Rick in your example above) in cell B9 I used the following formula =SUMPRODUCT(--($A$1:$A$6=A9),$B $1:$B$6). This is an array function so enter it by pressing ctrl+shift +enter. On Aug 15, 2:20 pm, Big Rick wrote: Hello Folks Please can u help! On my worksheet call GFSA, in any the the cells C8 to C29 I have a name. It could be Big Rick in C8 only, or Big Rick in C8, C12, and C20. What is require is a formula in another sheet (called Calc) that will total the value from the corresponding Q column. Eg. GFSA sheet Cell C8="Big Rick" the give me the total from Q8 but if Big Rick is in cells c8,c12, and C20 then give me the total from Q8, Q12 and Q20. Hoping you can understand this logic. Your help is and always has been very much appreciated. Thanking you in anticipation. -- Big Rick |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cells total help.
SUMPRODUCT just requires ENTER.
"Tim879" wrote: you can either use the sumif function or sumproduct I made a quick spreadsheet as follows: Col A Col B Rick 1 Car 2 Apple 3 Rick 1 Car 2 Apple 3 (i.e. a1 = Rick) SumIf in cell A8, I put the value I wanted to use in my sum (e.g. Big Rick in your example above) in cell B8 I used the following formula =SUMIF($A$1:$A$6,A8,$B$1:$B$6) SumProduct in cell A9, I put the value I wanted to use in my sum (e.g. Big Rick in your example above) in cell B9 I used the following formula =SUMPRODUCT(--($A$1:$A$6=A9),$B $1:$B$6). This is an array function so enter it by pressing ctrl+shift +enter. On Aug 15, 2:20 pm, Big Rick wrote: Hello Folks Please can u help! On my worksheet call GFSA, in any the the cells C8 to C29 I have a name. It could be Big Rick in C8 only, or Big Rick in C8, C12, and C20. What is require is a formula in another sheet (called Calc) that will total the value from the corresponding Q column. Eg. GFSA sheet Cell C8="Big Rick" the give me the total from Q8 but if Big Rick is in cells c8,c12, and C20 then give me the total from Q8, Q12 and Q20. Hoping you can understand this logic. Your help is and always has been very much appreciated. Thanking you in anticipation. -- Big Rick |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cells total help.
Works like a dream. Thank you very much.
You must all live on the planet Genius. I will have to go there on my holidays. -- Big Rick "Toppers" wrote: SUMPRODUCT just requires ENTER. "Tim879" wrote: you can either use the sumif function or sumproduct I made a quick spreadsheet as follows: Col A Col B Rick 1 Car 2 Apple 3 Rick 1 Car 2 Apple 3 (i.e. a1 = Rick) SumIf in cell A8, I put the value I wanted to use in my sum (e.g. Big Rick in your example above) in cell B8 I used the following formula =SUMIF($A$1:$A$6,A8,$B$1:$B$6) SumProduct in cell A9, I put the value I wanted to use in my sum (e.g. Big Rick in your example above) in cell B9 I used the following formula =SUMPRODUCT(--($A$1:$A$6=A9),$B $1:$B$6). This is an array function so enter it by pressing ctrl+shift +enter. On Aug 15, 2:20 pm, Big Rick wrote: Hello Folks Please can u help! On my worksheet call GFSA, in any the the cells C8 to C29 I have a name. It could be Big Rick in C8 only, or Big Rick in C8, C12, and C20. What is require is a formula in another sheet (called Calc) that will total the value from the corresponding Q column. Eg. GFSA sheet Cell C8="Big Rick" the give me the total from Q8 but if Big Rick is in cells c8,c12, and C20 then give me the total from Q8, Q12 and Q20. Hoping you can understand this logic. Your help is and always has been very much appreciated. Thanking you in anticipation. -- Big Rick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Total of cells | Excel Discussion (Misc queries) | |||
The total of a couple cells and only include one of the cells if | Excel Discussion (Misc queries) | |||
Grand total time between two total cells | Excel Discussion (Misc queries) | |||
In several cells I have =?+423.55 I want total w/o the 423.55. | Excel Worksheet Functions | |||
SUMIF - Can I total the value of cells if three other cells = set | Excel Worksheet Functions |