@sum(range)

  • Thread starter Thread starter NewUser
  • Start date Start date
N

NewUser

The spreadsheet I am using looks like this:

Name: Value
John Smith 1
Mary Jones 1
John Smith 1

I only want to count the value shown next to John Smith
one time. Giving me a total of 2.

Note: John Smith, could be listed multiple times; however,
the value shown next to each line will always be the
same.
 
Hi
try
=SUMIF(A1:A100,"John Smith",B1:B100)
if you want to sum the values. If you only want to count the number of
occurences of John Smith use
=COUNTIF(A1:A100,"John Smith")
 
Thanks for the reply. Not quite what I was looking for.

The spreadsheet being worked has approx 5,000 lines.
Throughout each of those lines I may have duplicate
records (ie John Smith, etc) appearing multiple times.

What I need to do is @sum(d1..d5000) and exclude from
that sum values where a customer's name is listed multiple
times.
 
Hi
sorry I misread your question. Try the following
- add a helper column with the following formula in C1
=IF(COUNTIF($A$1:$A1,A1)=1,"Use","")
copy this down for all rows

Now use the following formula
=SUMIF($C$1:$C$5000,"Use",$B$1:$B$5000)
 
sorry I misread your question. Try the following
- add a helper column with the following formula in C1
=IF(COUNTIF($A$1:$A1,A1)=1,"Use","")
copy this down for all rows

Now use the following formula
=SUMIF($C$1:$C$5000,"Use",$B$1:$B$5000)
Click to expand...
...

This is a speed-storage trade-off. First, if you're going to use a 'helper
column', make it something efficient, e.g., just

C1:
=COUNTIF($A$1:$A1,A1)

then simplify the SUMIF formula as

=SUMIF($C$1:$C$5000,1,$B$1:$B$5000)

Numeric comparisons are MUCH FASTER than string comparisons.

The formulas above require 5000 additional cell formulas. That's going to eat at
least 40KB of storage (using an extremely optimistic estimate of 8 bytes for
each formula). These formula save time by only looking backward through col A.

The smaller storage but slower recalc alternative involves avoiding 'helper
columns' in the first place. One work-alike for the formula above would be

=SUMPRODUCT((MATCH($A$1:$A$5000,$A$1:$A$5000,0)=ROW($A$1:$A$5000)
-CELL("Row",$A$1:$A$5000)+1)*$B$1:$B$5000)

An alternative based on a different interpretation of the OP's specs, i.e.,
exclude all entries for which the col A entry appears more than once in col A,
even the *FIRST* such entry, would be

=SUMPRODUCT((COUNTIF($A$1:$A$5000,$A$1:$A$5000)=1)*$B$1:$B$5000)
 
....
C1:
=COUNTIF($A$1:$A1,A1)

then simplify the SUMIF formula as

=SUMIF($C$1:$C$5000,1,$B$1:$B$5000)

Numeric comparisons are MUCH FASTER than string comparisons.
Click to expand...

Hi Harlan
totally agree but I choose the formula to make it clearer to the OP. Of
course using numeric values is faster :-)
=SUMPRODUCT((MATCH($A$1:$A$5000,$A$1:$A$5000,0)=ROW($A$1:$A$5000)
-CELL("Row",$A$1:$A$5000)+1)*$B$1:$B$5000)
Click to expand...

Nice one. Thought about something similar myself but wasn't able to
come up with such a solution

Wish you a nice evening
Frank
 
Back
Top