If then Count

[TankGirlCCSU]

I am trying to maximize efficiency for a spreadshet report that I do monthly. One column is the name of the insurance company and another column is the number of days spent in a bed. I would like to count the number of occurrances of the insurance and then total the number of days for that insurance. I cannot seem to get the formula right. Can anyone assist?

As the example below shows, there are multiple occurrances of the same insurance, which are not grouped. I know that I can sort the data but I am trying to find a formula that will take care of it all. Thanks in advance!

COLUMN B COLUMN J
Insurance A 2
Insurance C 3
Insurance L 1
Insurance A 3
Insurance M 2
Insurance C 5

[Palmetto]

You could use a Pivot Table or use the SUMIF function.

=SUMIF($B$1:$B$6,X1,$J$1:$J$6)

Where X1 is the criteria cell for the count, i.e. Insurance A, etc,

[TankGirlCCSU]

Is there a way to make X1 be a phrase, such as the insurance name, instead of an actual cell?

[Palmetto]

Yes. Just replace X1 with "Insurance A" (including the quotes).

However, I would create a list of the insurance companies and reference the cells containing the name as this prevent having to "hard-code" the name into the formula.

[TankGirlCCSU]

Is there a way to make the Insurance column a drop down list that I can populate in order to prevent typing errors? The fiscal folks don't want to re-create their spreadsheet so I have to account for those problems.

Thanks in advance!

[Palmetto]

Use Data Validation, list option.
If the list is a short one type the list items directly into the source box in the dialog with each item separated by a comma.

If it is a long list, then it is generally easier to maintain the list on a separate worksheet.
If you need this option, create a dynamic named range for the list and reference the named range in the source box like this: =MyList

Where "MyList" might refer to this named range formula: =OFFSET(Sheet2!$A$1,0,0,counta($A:$A),1)
Above formula assumes list is kept in column-A cells on a sheet named "Sheet2".

[TankGirlCCSU]

You are a fantastic help! Last question And it is an easy one for you, I am sure!

Column B contains the names of the insurance companies. Each row is the actual person who occuppied a bed in a given month. Is there a way (without sorting the data) to use COUNT to total the number of people with each insurance for the entire spreadsheet? I imagine that I would need to use the quotes method again but am unsure.

Thanks (again) in advance!

[Palmetto]

I would use a Pivot Table.

Or you could create a unique list containing the insurance companies and use the COUNTIF function.
If the list of insurance companies was in the range of Z2:Z20, then we might use:

=COUNTIF($B$2:B$B100,Z2)

Obviously, adjust the range references.

If that doesn't get you what you need, post a sample workbook (fake data, exact structure) and show a few example of the desired results.

[TankGirlCCSU]

OK so forget everything else I wrote and let's start over I have completed the spreadsheet and all the various data feeds but I still have one issue that is currently being counted by hand:

Column G is the date of admission. Sometimes people come in at the end of a month but aren't discharged until the next month. This is a monthly report so I need to separately count the days that carry over from the previous month. Is there a way to extrapolate the data so that it adds up all of the days in Column G that are before a certain date? I can count how many times it happens but I cannot total the number of days.

[Palmetto]

It will be more helpful if you upload a sample workbook (dummary data, exact structure) to show what you mean along with an example or two of the desired results.
The answer depends on exactly what you have in column-G cells - are they numbers or date values?

Possibly:

=COUNTIF($G$1:$G$100,"<3/1/2012")

[TankGirlCCSU]

Here is an example of what I have so far with some raw data in it. What would be most helpful is if, on the second tab ("Insurance Bed Days") in row 67, there was a way to count by insurance category how many days total for each category were from the previous month. I can count the cells that contain that data but I can't get it to give me total number of days. I have highlighted the area in red.

[Palmetto]

I can likely develop the formula, however, you haven't provided any logic about the insurance categories, specifically, the criteria that is used to determine which group insurance companies belong to.

It would helpful if you included an explanation of how you manually determined "2" is correct for "Other (Commerical)" and "3" is correct for "Reg Medicaid", etc.

Are both columns G & H (Data entry sheet) to be considered?

[TankGirlCCSU]

The insurance companies groups are determined by our managed care folks. There really isn't any logic to it other than just knowing which group they belong to.

Only the date of admission needs to be considered. If a client is admitted and carries over to the next month, they will be on the next monthly report so it doesn't really need to be tracked otherwise.

Thanks again! You are the best

[Palmetto]

What you asked for is:

What would be most helpful is if, on the second tab ("Insurance Bed Days") in row 67, there was a way to count by insurance category how many days total for each category were from the previous month

If only certain people "know" how to group the companies then there must be some logic or criteria by which they do this.
Unless *YOU* also know this, how can you (or anyone else who doesn't have the "tribal knowledge" to) determine proper group assignlents?

I don't think I can be of much further help unless you can provide a clear and explicit explanation of the groupings or you change your requirements.

[TankGirlCCSU]

Oh I think you can still help me

Let's simplify it: is there a way to count the number of days from a previous month? I can count the number of cells but I want to count the actual number of days. I think I can fix it from there if I knew that formula.

[Palmetto]

Working with just the "red" cells on the Data Entry sheet, maybe you could use:

=MAX('Data Entry'!H186:H197)-MIN('Data Entry'!G186:G197)
Format the formula cell as a number.

However, I've no clue as how you decided which cells to highlight seeing as some of the non-highlighted cells also contain the same date values.

You might take a look at using the DATEDIF function - Chip Pearson.

DATEDIF - Microsoft