Calc to sum like terms only once in list

[TPS Reporter]

Is there some logic that can be written to only add like subjects once...example:

bob 4
scott 3
Joe 4
bob 4

I need to have the total to be 11 not 15. can somebody help with writting a statement to solve this for me...is this an if then statement?? Thanks so much for your help

 

[Oaktree]

How would you handle:

bob 4
scott 3
Joe 4
bob 5

Or would this not occur in your data?

 

[TPS Reporter]

This situation would not (should not) occur in my data set. lets assume that this wouldn't happen.

 

[NBVC]

You could add a helper column, to eliminate or Zero the repeats and some those up. Then you can hide the column.

Book1
	A	B	C	D
20	bob	4	4
21	scott	3	3
22	Joe	4	4
23	bob	4	0
24			11
Sheet1

Formula in C20: =B20
Formula in C21: =IF(COUNTIF(A$20:A20,A21),0,B21), copied down the rest of the way.
Formula at bottom of column: =SUM(C20:C23), which can be placed anywhere

 

[just_jon]

Book1
	A	B	C	D
1
2	bob	4	11
3	scott	3
4	Joe	4
5	bob	4
6
Sheet1

The above is an array formula, which requires entry via Control+Shift+Enter [CSE], not just Enter. Any direct edit of the formula will require re-entry via CSE. You can tell if the formula has been entered correctly, as it will have braces - { } - around it afterward.

 

[TPS Reporter]

Just jon, I'm working with your formula and I'm not getting it to work properly...i don't htink i'm understanding the CSE part because I recreated your exact table, copied the formula and i'm not getting the braces and its giving me the answer of 2.

 

[just_jon]

Just jon, I'm working with your formula and I'm not getting it to work properly...i don't htink i'm understanding the CSE part because I recreated your exact table, copied the formula and i'm not getting the braces and its giving me the answer of 2.

You have to hold down both the SHIFT and CONTROL keys when pressing the ENTER key after having entered that formula in the formula box.

You can copy/paste it into the box from here, then place your cursor at the far right side of the formula, then hold down the 2 keys while hitting ENTER.

 

[TPS Reporter]

Thanks...it works now...can you please give me some background to the CSE function because I don't understand what exactly i just did here...