+ Reply to Thread
Results 1 to 5 of 5

Summing Values Based on Text Criteria

  1. #1
    Forum Contributor
    Join Date
    12-07-2004
    Posts
    596

    Summing Values Based on Text Criteria

    Problem:

    Each row of columns A & B contains text and a corresponding number.
    We want to sum all the numbers in column B corresponding with the text values in column A that meet the following criteria:
    1. Text is \"Excel\".
    2. Text starts with \"Excel\".
    3. Text ends with \"Excel\".
    4. Text contains \"Excel\".
    5. Text length is 3 characters.

    Solution:

    Use the SUMIF function as shown in the following formulas:
    1. =SUMIF(A2:A7,\"Excel\",B2:B7)
    2. =SUMIF(A2:A7,\"Excel*\",B2:B7)
    3. =SUMIF(A2:A7,\"*Excel\",B2:B7)
    4. =SUMIF(A2:A7,\"*Excel*\",B2:B7)
    5. =SUMIF(A2:A7,\"???\",B2:B7)

  2. #2
    Registered User
    Join Date
    10-31-2005
    Posts
    2

    SUMIF help

    Hi everyone,

    I am creating a timesheet for the employees at the clinic where I work. The timesheet sums both the total balance due and the amount collected from each client. They want to be able to have a client listed with the amount due from previous months without the amount collected from previous months, but both are figured from the same column. The balance is determined by a formula that is dependent upon the value in the "collected" cell (cost-collected=balance). Is there a way to mark the cell containing the collected amount so that I can sum only the numbers from the present month while still being able to sum the balance for the client's history? I'm thinking like to not sum numbers that are bold, have an asterisk before them, or are in filled cells. Any help would be greatly appreciated. Thanks.

    Eric

  3. #3
    Registered User
    Join Date
    08-14-2003
    Location
    New Zealand
    Posts
    41

    Reply: ejchis

    Hi ejchis,

    Quote Originally Posted by ejchis
    Hi everyone,

    I am creating a timesheet for the employees at the clinic where I work. The timesheet sums both the total balance due and the amount collected from each client. They want to be able to have a client listed with the amount due from previous months without the amount collected from previous months, but both are figured from the same column. The balance is determined by a formula that is dependent upon the value in the "collected" cell (cost-collected=balance). Is there a way to mark the cell containing the collected amount so that I can sum only the numbers from the present month while still being able to sum the balance for the client's history? I'm thinking like to not sum numbers that are bold, have an asterisk before them, or are in filled cells. Any help would be greatly appreciated. Thanks.

    Eric
    I suggest you use an array formula something like this:

    {=TotalCost-SUM((A1:A10="Collected")*(B1:B10))}

    HTH,

    Alan.
    To help us help you, try to do the following:

    1) Be precise about what you want to do, and provide a sample of your data / inputs - exactly as they are.

    2) State the formula(e) / code that you have tried. People are happy to help , but if you haven't even given it a go, you are less likely to get help, or the help you get will be very basic.

    3) State the results you are getting from your formula(e) / code already.

    4) State the outputs that you *want* to be getting.

  4. #4
    Registered User
    Join Date
    10-31-2005
    Posts
    2

    cost changes

    Hi Alan,

    Thanks for your response. I should have added this detail in my original post. the situation is complicated because I do not have a consistent cost for sessions, even for sessions for the same clients. so I need to enter the cost independently for each client's session. Right now I have created my own formula using macros, but this is difficult to explain to a group of people who are not very computer savvy. The formula I have created doesn't include numbers that are highlighted in a color other than white. Unfortunately, they have to update all forumlas to get their numbers to come out rather than just entering the data and seeing the results in the total columns. Any other thoughts?

  5. #5
    Registered User
    Join Date
    08-14-2003
    Location
    New Zealand
    Posts
    41

    Reply: ejchis

    Hi ejchis,

    Quote Originally Posted by ejchis
    Hi Alan,

    Thanks for your response. I should have added this detail in my original post. the situation is complicated because I do not have a consistent cost for sessions, even for sessions for the same clients. so I need to enter the cost independently for each client's session. Right now I have created my own formula using macros, but this is difficult to explain to a group of people who are not very computer savvy. The formula I have created doesn't include numbers that are highlighted in a color other than white. Unfortunately, they have to update all forumlas to get their numbers to come out rather than just entering the data and seeing the results in the total columns. Any other thoughts?
    It is quite difficult to help without some specifics of what you are lookig at.

    Can you post some sample data, with the quantified result that you want to achieve?

    Alan.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1