1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel: making SUMIF case sensitive

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

Thread Status:
Not open for further replies.
Advertisement
  1. robrien418

    robrien418 Thread 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.
     
  2. Sponsor

  3. Ent

    Ent Trusted 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.
    http://www.accountingweb.co.uk/anyanswers/sumif-excel
    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. Keebellah

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

    Keebellah Trusted 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. Ent

    Ent Trusted 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. Keebellah

    Keebellah Trusted Advisor

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

As Seen On
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.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1012671

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice