Multiple variables in formula

[repke]

I have a table that lists by weekday for a month the cases a staff person is assigned. The data in the table includes the patient's name, the staff person's initials, the reason the patient is here, and a code (i.e., 0-5) that is related to the reason the patient is here. This comprises 4 columns for each day Monday through Friday. I want to have a separate summary sheet that lists by week 8 different variables (e.g., number of cases, felony cases, etc) for each staff person (10 staff total). In the summary sheet, I have multiple variables I'm trying to count in each cell. For example, in one cell I want to figure out how many "cases" that each staff person did for a given week. So I tried making a countif statement but the formula gets to be very cumbersome. I tried naming a range to simplify it but it didn't work in the formula (i.e., Staffweek1 - A1:A5, E1:E5, I1:I5). Any help in how to simplify the formulas would be appreciated.

[shg]

You need to post a workbook with some redacted sample data and explain your desires in context.

[Dunc3142]

Also let us know what version of Excel you are using.
XP
2003
2007???

[repke]

I attached a workbook with questions in it. I'm using excel 2003.

[repke]

Hi,

I've attached a workbook with the data I need to calculate. In my situation, each day staff are assigned a new patient, the number of patients varies per day with the max usually being 9 patients per day.

In the Summary worksheet I've tried using COUNTIF to count how many cases are assigned to each staff person as well as counting how many of each type are assigned but in order to do this I would have to nest (correct term?) several functions to include a full week, isn't there an easier way to do it?
I've tried naming a range Staffw1 but it didn't work in the formula (see Summary C3). I get a #VALUE error. Is there a way to name the ranges that would work because that would be much easier to enter then referring to each column?

I'd really appreciate your help.

[ratcat]

G'day repke,

This is my version.

I had to rearrange the "April2008" sheet (my version is the "April2008(2)).

Just click in the cell in the summary sheet to see what formulas (two formulas) are being used.

I wasn't quite sure what data had to be matched up. Charge or Code.

Just post or PM me for more questions.

Hope that helps

[repke]

Thank you so much, I had not thought of arranging the data this way but it works great. I actually want to track the 6 case "Types" in the Summary sheet. For the sake of providing a sample I only listed 6 "Charges" but actually there are about 500 charges (e.g., arson, assault, etc). So in the real workbook I have the 500 charges in a dropdown menu (using data validation) and when you select a particular charge then the "Code" column is automatically entered (and I hide the code column so other users don't change it).

Type3 cases includes 4 different charges (Charge1, Charge3, Charge4, Charge5) and so I couldn't figure out how to count those 4 different charges for each staff person (e.g., AA, BB, etc) for each week. For example, in the Summary sheet I wanted to know how many Type3 cases that staff person "AA" was assigned in the first week and so on for each staff person for each week. Any help would be greatly appreciated.

Thanks again!

[ratcat]

G'day Repke,

How about this for an idea.

I had to adjust the "Type 3" formula, base on my understanding of your requirements.

=sumproduct(.......) /4

This is base on this eg

1 x Type 1 = One Charge
1 x Type 2 = One Charge
2 x Type 3 = Eight Charges

This means on the "April2008" sheet you have to enter all 4 Charges related to Type 3. When the "Summary" sheet counted the "type" base on the example above the count will return two not eight. (See the attachment)

Also on the attachment from row 40, a count of all charges by the staff member, and row 36 & 37 is a lookup. Type in the number of the charge and it will show the total count of that charge. This should make things easier a specially with 500 charges to look at. OMG.

You will have to unhide column A in the "Summary" sheet to see the formula interaction.

The best thing you can do is play around with your raw data to see if your getting the right results back.

Again post or PM if more information is needed.

Cheers