Cells total help.

[Guest]

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.

 

[Guest]

=SUMPRODUCT(--(GFSA!C8:C29=X1),GFSA!Q8:Q29)

X1="Big Rick"

 

[Peo Sjoblom]

=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

 

[Tim879]

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.

 

[Guest]

SUMPRODUCT just requires ENTER.

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.

 

[Guest]

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.