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)
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
Hi ejchis,
I suggest you use an array formula something like this:Originally Posted by ejchis
{=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.
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?
Hi ejchis,
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.
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks