» Summing Values Based on Text Criteria

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,

[QUOTE=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[/QUOTE]I suggest you use an array formula something like this:

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

HTH,

Alan.

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,

[QUOTE=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?[/QUOTE]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.