Calculating sum of count based on selected codes multiplied by code value

[Doug Mutzig]

Good afternoon all,

First off let me apologize for the title, I tried to describe what is needed but it still sounds confusing.

Background information:

I have 3 tables:
1. Shift code legend (contains 3 columns - Code, Description, FTE value). table is named: T_ShiftCode
2. Hours Calculating Reference (Contains 13 columns - for this issue only 2 are neede: LPN Codes and LPN Value. Table is named: T_HOURS
3. On a different tab I have another table containing multiple columns including Staff Name, Service, Skill, Day1, Day2, etc. This table is a schedule for 4 weeks

They way things currently work is the end user can add/remove Shift codes so that they can tailor for their department/unit. They then give each code an FTE value (4hrs= .5 FTE value, 8hrs= 1 FTE value, and 12hrs = 1.5 FTE value)

Next the end user will enter the codes they want associated with a particular Skill (LPN, RN, etc.) in the HOURS Table. When they enter in the code there is a second column that auto-populated the FTE value from the Shift code table.

Finally on the Schedule the end user will enter a code for each staff member for each day.

For example I have 5 staff members (Andy, Betty, Carl, Dan, Ed). I have 6 shift codes (1,2,3,4,CH,NM) with different FTE values (1 through 4 are .5, CH = 1, and NM=1.5) which I enter on the SHift Code legend. Andy, Betty, Ed are LPNs and on the schedule for Day 1 I enter the code 2 for Andy, CH for Betty, and 3 for Ed.

What I am trying to do is get an accurate count of FTEs for the day (i.e. Andy = 2 = .5 + Betty = CH = 1, Ed = 3 = .5 for a total FTE count of 2) .

Thinking through the logic I have:
1. For Day 1 find all of the LPNs (in the example 3 LPNs for day 1)
2. Sum the number of LPNs for each code ( code 2 = 1 LPN, code CH = 1 LPN, code 3 = 1 LPN)
3. For each code get the FTE value
4. Multiply each sum of LPNs per code by code's FTE value ( 1 * .5 (code 2) + 1*1 (code CH) + 1*.5 (code 3) = 2)

but for the life of me I cannot seem to get anything to work. I have tried vlookups, Index/matches, sumproduct and countifs and just cant get anything other than an error.

I can get an accurate count of people using:

=SUMPRODUCT(COUNTIFS(T_P1Dtest[D1],R_LPNs,T_P1Dtest[Skill],"LPN"))

I can get the FTE value using:

=VLOOKUP(T_P1Dtest[D1],T_HOURS,3,FALSE)

But trying to join them to do a sum for the entire day does not work

I have tried:

=SUMPRODUCT(COUNTIFS(T_P1Dtest[D1],R_LPNs,T_P1Dtest[Skill],"LPN")*INDEX(T_HOURS,MATCH(R_LPNs,R_T_DUTY,0),3))

receiving a #Value ! error. It seems that the R_LPNs (range of the LPN column in the HOURS table) does not resolve correctly when I evaluate the formula. I have also tried T_FTE[LPN Codes] with the same result.

Does anyone have an advice on how to tackle this? I am trying to not hardcode the shiftcodes into the formulas as things change often and I want to make it so that the end user can update a table entry or two and still have the calculations work.

Please let me know if I have forgotten to provide some information, and THANK YOU for any and all help you can provide.
Doug

 

[Zack Barresse]

In your formula, you're trying to compare a singular result with an array result which will always return an error unless your array returns a singular value. That's what SUMPRODUCT is doing - comparing arrays. I'm also not sure what values are in 'LPN Codes' and 'LPN Value' so it's hard to offer an alternative formula.

For your formula to work, you could try replacing your COUNTIF with an array formula (i.e. "COUNT(IF(condition,return))"). I'm not 100% following which columns contain which values though. You could also use a helper column to retrieve the FTE values from the associated code and then sum those.

 

[Doug Mutzig]

Hi Zack,

Ok let me see if I can add some more info:

LPN Codes column contains the abbreviated Shift code enter in the Shift Code table (Text). For example the Shift code table would have (where - denotes column) CH - Charge Nurse - 1 . CH is the Shift Code, Charge nurse the description, and 1 is the FTE value.

In the HOURS table there are two columns titled LPN Codes and LPN Value. The LPN Codes column with hold those codes from the Shift Code table that the end user wants to apply to LPNs. The LPN Value column would show the FTE value for the selected code.

I have done this as there are several areas the Shift Code is applicable (i.e. the shift code 1 could be used for RNs, LPNs, TECHs, etc.)

I am not sure how I would utilize the countif as I need to compare the shift codes entered into the schedule and see if any match the codes listed in the LPN column, and then limit the results based a match in the Skill column with LPN. Then take the codes found, count them and multiply them by their fte value.

I wish I could add a diagram to help with this as I am looking at the screens and what I am typing and still end up confused.

I was thinking I could break it up into 2 parts:
1. A sum of the LPNs
2. A value for the total FTE (i.e. .5 + 1 + .5)

and then multiply the 2 together to get the total FTE (however I am not sure if that would work). 3 LPNs * 2=6 which is incorrect. I would need to to a sum of LPNs by each code and multiply that by the FTE value for the code which makes me think I need some kind of intermediate table?

 

[Doug Mutzig]

Good evening all!

I am still trying to work this out and have a couple of formulas I believe are close but just miss the mark:

To recap:
I have 1 table that has 3 columns (Code, Description, FTE value), I have another table that is a schedule where staff can fill out different codes for staff members per day. The schedule has the members name and Skill listed (ex. RN, Tech, MSA, LPN, etc.) What I am trying to do is per day look up the lookup all staff with a specific skill, then lookup their listed codes and find their FTE value to then be summed up.

For example if I have 3 RNs and their codes for day 1 are (1,4,4) I would like to get to get a 4 back as a result (code 1 = 1 FTE, code 4 = 1.5 FTE so 1 + 1.5 + 1.5 = 4).

I have been able to accomplish this with a helper table that does each skill (current 4 but could increase) per each code (currently 70 but could increase) per day. However, doing this for 28 days for both a day and night shift (56 days) per tab and 13 tabs brings the workbook to a crawl.

I have also tried the following formula (not sure if they are supposed to be in code window or not, but going to do it to be safe:

=SUMIFS(T_HOURS[FTE Val],T_P1Dtest[Skill],D21,T_P1Dtest[D1],R_Codes)

However the codes fails on the last part R_Codes (Range on the table for the codes), I have also tired T_HOURS

 and also failed. I am assuming this is due to a range being entered instead of a specific value.

I then tried a different tactic of using LOOKUP and received some promising results:
[CODE]=SUMIFS(T_HOURS[FTE Val],T_P1Dtest[Skill],D21,T_P1Dtest[D1],N42)

I do receive a result, but it is the sum of all the FTE values in the table and I need it limited by the selected Skill level. I used LOOKUP so that I would not have to do an array formula; however, if that is what it takes I go with it. Though I have yet to get an array formula to work.

Does anyone have any ideas on what I can do for this without having helpers going behind the scenes?

 

[Eric W]

This is definitely a case where a picture is worth a thousand words. It took me quite a while to try to build a sheet based on your description. Let's see if I came close:

	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Code	Description	FTE Value		LPN Codes	LPN Value		Staff Name	Service	Skill	Day1	Day2
2	1	a1	0.5					Andy		LPN	2	NM
3	2	a2	0.5					Betty		LPN	CH
4	3	a3	0.5					Carl			1
5	4	a4	0.5					Dan
6	CH	a5	1					Ed		LPN	3	CH
7	NM	a6	1.5
8
9								Daily LPN FTE Total			2	1.5

<tbody>
</tbody>

Sheet7​

Worksheet Formulas Cell Formula K9 =SUMPRODUCT(SUMIF(T_ShiftCode ,K2:K6,T_ShiftCode[FTE Value]),--($J2:$J6="LPN"))[/TD] [/TR] </tbody>[/TABLE] [/TD] [/TR] </tbody>[/TABLE] I'm not sure what the second table is for. <tbody>

<tbody>

 

[Doug Mutzig]

Hi Eric,

That worked perfectly!!!! Thank you very much for your help on this!

Two follow up questions:

1. What do the -- in the formula mean?
2. For the part of the formula where it is hard coded to look for "LPN" is it possible to change it to look at the first 2 characters in cell? I was thinking something like ($J2:$J6=LEFT(N7,2)) but that doesn't seem to work. I am thinking because the look up value is the full text.

Thank you again for all your help on this!
Doug

 

[Eric W]

The SUMPRODUCT works by creating multiple arrays, multiplying the equivalent elements from each array together, then adding the results. In this case, the first array is the SUMIF for each row in K.

SUMIF(T_ShiftCode

,[/COLOR][COLOR=#ff0000]K2[/COLOR][COLOR=#333333],T_ShiftCode[FTE Value])
[/COLOR][COLOR=#333333]SUMIF(T_ShiftCode[Code],[/COLOR][COLOR=#ff0000]K3[/COLOR][COLOR=#333333],T_ShiftCode[FTE Value])
[/COLOR][COLOR=#333333]SUMIF(T_ShiftCode[Code],[/COLOR][COLOR=#ff0000]K4[/COLOR][COLOR=#333333],T_ShiftCode[FTE Value])
[/COLOR][COLOR=#333333]SUMIF(T_ShiftCode[Code],[/COLOR][COLOR=#ff0000]K5[/COLOR][COLOR=#333333],T_ShiftCode[FTE Value])
[/COLOR][COLOR=#333333]SUMIF(T_ShiftCode[Code],[/COLOR][COLOR=#ff0000]K6[/COLOR][COLOR=#333333],T_ShiftCode[FTE Value])

which resolves to {.5,1,.5,0,.5}.

The next array is:

[/COLOR][COLOR=#333333]$J[/COLOR][COLOR=#ff0000]2[/COLOR][COLOR=#333333]="LPN"
[/COLOR][COLOR=#333333]$J[/COLOR][COLOR=#ff0000]3[/COLOR][COLOR=#333333]="LPN"
[/COLOR][COLOR=#333333]$J[/COLOR][COLOR=#ff0000]4[/COLOR][COLOR=#333333]="LPN"
[/COLOR][COLOR=#333333]$J[/COLOR][COLOR=#ff0000]5[/COLOR][COLOR=#333333]="LPN"
[/COLOR][COLOR=#333333]$J[/COLOR][COLOR=#ff0000]6[/COLOR][COLOR=#333333]="LPN"

which resolves to {TRUE,TRUE,FALSE,FALSE,TRUE}.  The "--" is called a "double unary".  Any time you use a Boolean value (TRUE/FALSE) in an equation, Excel coerces it to a value (1/0).  So putting a - in front of it is like multiplying by -1, and doing it twice is like multiplying by -1 twice, which is 1.  So long story short, putting -- in front of TRUE/FALSE values converts them to 1/0, and now SUMPRODUCT can multiply them together.

So we have [/COLOR][COLOR=#333333]{.5,1,.5,0,.5} times {1,1,0,0,1} = {.5,1,0,0,.5}, and we add those up to get 2.

As far as checking for the first 2 characters only, something like

[/COLOR][COLOR=#333333]--(LEFT($J2:$J6,2)=LEFT(N7,2))

should work.[/COLOR]

 

[Doug Mutzig]

Thank you very much for the great explanation and the additional code. I'll give it a try, and add the -- to the toolkit