# Excel: making SUMIF case sensitive

Discussion in 'Business Applications' started by robrien418, Aug 15, 2011.

Not open for further replies.
1. ### robrien418Thread Starter

Joined:
Jul 15, 2011
Messages:
5
Hi,

Im trying to create a formula that will make my SUMIF function case sensitive. Im trying to add the cost of different usernames. For example.."JSmith and jsmith"..they are different names with different costs but excel is adding them as one instead of separate.

Thanks.

3. ### EntTrusted Advisor

Joined:
Apr 11, 2009
Messages:
5,465
First Name:
Josiah
This thread in another forum suggests using the Exact function to make a second column for comparing them.
I don't have access to Excel and can't say whether it would work, but it's worth a shot.

I suppose the alternative would be to write your own macro based function that does things as you want them done.

4. ### KeebellahTrusted Advisor

Joined:
Mar 27, 2008
Messages:
6,492
First Name:
Hans
I think you will have to work this out with a custommade function
You cannot SUMIF string values, in that case you need COUNTIF.
But comming back to your question, a self written function could do the job.

5. ### KeebellahTrusted Advisor

Joined:
Mar 27, 2008
Messages:
6,492
First Name:
Hans
You could try this
Code:
```Function CountExact(myVal As Variant, myRange As Range) As Integer
Dim rng As Range, mycount As Integer
For Each rng In myRange
If rng.Value = myVal Then mycount = mycount + 1
Next rng
CountExact = mycount
End Function
```
Put the function in the column you need,
For example:
In cell G1=countexact(F1,\$F\$1:\$F\$4)

This will count the occurrence of F1 in the range F1:F4
Of course in jsmith occurs more than once you will have jsmith twice as a count of 2

jsmith 2
Jsmith 1
jsmith 2
PSmith 1
JSmith 1

You can overcome this by using the function on a unique range of values

6. ### EntTrusted Advisor

Joined:
Apr 11, 2009
Messages:
5,465
First Name:
Josiah
Why can't you use Sumif with strings?
In the if section you check whether the string (in this case the username) matches, and have the sum section counting up an adjacent column (in this case the cost of that name).

7. ### KeebellahTrusted Advisor

Joined:
Mar 27, 2008
Messages:
6,492
First Name:
Hans
My mistake, I used it incorrectly. Sorry
I stand corrected

As Seen On

### Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

over 733,556 other people just like you!