Counting items in a grid using index, match and left

[mark hansen]

I have a schedule that has people down the left side, and dates across the top. We enter our schedule using single letter codes such as “C”, “L” etc and the daily entry is something like “C L” (or “C{space}L”) to indicate they are in clinic for the AM and Lab for the PM. Some entries are C{space}C for clinic in the AM and PM.

I need to count up how many “C “ and “ C” there Are for a person in a given date range. I made a grid on another sheet that has the left column matching the list of names in the schedule sheet. Across the top are the codes used in the schedule and I have a beginning and ending date.

So, four variables – beginning date, Ending date, person’s name and schedule code.
The entire schedule range name is “SCHEDULE”.
The Providers range names in the Schedule sheet is “sProvider”
The dates range name in the schedule sheet is sDates

So far I have a formula =LEFT(INDEX(SCHEDULE,MATCH(CA27,sProvider,0),MATCH(CA21,sDate,0)),2)
This looks at the provider name (CA27) and the beginning date (CA21) and returns what I expect (C{space}). (I usually build long formulas from the inside out, but got stuck when it came time to get the range for the countifs).

Problem (I think)
I need to look at the row with the providers name in the SCHEDULE range beginning at the beginning date through the ending date and count how many times C{space} is in the first 2 characters of the cells in the range.

How would I define the range for the countifs? I’ll be adding that number to the amount of times I find {space}C is found in the right two characters in same range.

If you are wondering why I can’t just count the letter “C” it’s because some schedule codes are “CA” for an all-day code. That’s why they use a space to separate AM and PM.

Thanks for any help or insight.
Mark

 

[Peter_SSs]

It isn't clear to me exactly how your ranges are named or whether you want a combined count for morning & afternoon or separate.
In any case this may get you going. I have done a combined count & have done it on a single sheet but you should be able to adapt to two sheets.
Post back with more exact details and/or sample data & expected results if this is incorrect or insufficient.

Formula in B10 is copied across and down.

Excel Workbook
	A	B	C	D	E	F	G	H	I
1		01-Apr-16	02-Apr-16	03-Apr-16	04-Apr-16	05-Apr-16	06-Apr-16	07-Apr-16	08-Apr-16
2	Name 1		C L	C C	CA L			L C
3	Name 2				L C	L L			C C
4	Name 3			C L	C CA
5
6
7		Start	02-Apr-16
8		End	06-Apr-16
9		C	L	CA
10	Name 1	3	2	1
11	Name 3	2	1	1
12	Name 2	1	3	0
13
Schedule

#VALUE!

</td></tr></table></td></tr></table>

 

[PaddyD]

Can you give us a complete list of the schedule conditions you're trying to count...

 

[mark hansen]

Thanks so much Peter, that worked perfectly! Your example made it very easy to change the cell references to fit into my worksheet. I appreciate you quick and valuable help.

Paddy, currently they have 21 schedule codes for 21 locations (or coverage tasks) they need to get their people into. Some are considered undesirable so they will be using these counts to attempt to level the undesirable tasks across all of their staff.

Peter thanks again for your help.
Mark

 

[Peter_SSs]

Thanks so much Peter, that worked perfectly! Your example made it very easy to change the cell references to fit into my worksheet. I appreciate you quick and valuable help.

You are very welcome. Thanks for the follow-up.