# Calc to sum like terms only once in list

#### TPS Reporter

##### New Member
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

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
How would you handle:

bob 4
scott 3
Joe 4
bob 5

Or would this not occur in your data?

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

You could add a helper column, to eliminate or Zero the repeats and some those up. Then you can hide the column.
Book1
ABCD
20bob44
21scott33
22Joe44
23bob40
2411
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

Book1
ABCD
1
2bob411
3scott3
4Joe4
5bob4
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.

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.

TPS Reporter said:
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.

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...

Replies
5
Views
350
Replies
6
Views
939
Replies
1
Views
287
Replies
2
Views
323
Replies
1
Views
202

1,203,171
Messages
6,053,874
Members
444,692
Latest member
Queendom

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back