Tough one - Sum and IF with multiple referencing (array)

[ronlim]

Been at it for a while... still trying to figure it out

All fields marked * are to be modified, while those marked otherwise
are static

I am working on a duty roster table that looks like this:

Name* - Shift* - Mon* - Tue* - Wed* - *etc etc etc
dude1 - shft1- D1 - D1 - D3 <---- let's call these Markers
dude2 - shft1 - D2 - D2 - D2
dude3 - shft2 - N1 - N1 - N2
dude4 - shft3 - D2 - D2 - D1

I have a legend table that says what D1, D2, N1, etc means
like so:

Leg - DAYS* - desc
D1 - 1.0 - Day shift
D2 - 0.5 - Day shift (half)
D3 - 1.0 - Day shift (training)
N1 - 1.0 - Night shift
N2 - 0.5 - Night shift (half)

where DAYS denotes how whether it is a full day or half day shift

Now I have a Headcount table to list the number of people that are
planned for duty:
Shift* - Mon - Tue - Wed
shft1
shft2
shft3

Now in the Headcount table, I want to count how many people are working
on each day who belong to a Shift by first matching their Markers to the
Legend for the number of days, then summing them up.

Sample DESIRED result, based on info provided above:
A - B - C - D
1 Shift* - Mon - Tue - Wed
2 shft1 - 1.5 - 1.5 - 1.5
3 shft2 - 1.0 - 1.0 - 0.5
4 shft3 - 0.5 - 0.5 - 1.0

I'm currently using Array Formulas with the IF function, but I'm having
trouble matching the Markers to the legend table.
This is in cell B2:
{=SUM(IF(<roster shft column range>=A2, IF(ISNUMBER(SEARCH("D?",<roster
date range>)),VLOOKUP(<roster Mon column>,<legend table>,2,false),o)))}

This only works halfway cause the VLOOKUP doesn't match all the Markers
in the array with the Legend. Instead uses the value of the first
Match.
So it uses the value for "shft1" on "Mon" which is D1, and counts the
it as 2.0 instead of 1.5 (D1 = 1.0) + (D2 = 0.5).

Help anyone?

 

[Domenic]

Assumptions:

A1:E5 contains...

Name - Shift - Mon - Tue - Wed
dude1 - shft1- D1 - D1 - D3
dude2 - shft1 - D2 - D2 - D2
dude3 - shft2 - N1 - N1 - N2
dude4 - shft3 - D2 - D2 - D1

A10:C14 contains...

Leg - DAYS - desc
D1 - 1.0 - Day shift
D2 - 0.5 - Day shift (half)
D3 - 1.0 - Day shift (training)
N1 - 1.0 - Night shift
N2 - 0.5 - Night shift (half)

A2023 contains...

Shift - Mon - Tue - Wed
shft1
shft2
shft3

Formula:

B21, copied down and across:

=SUMPRODUCT(--($B$2:$B$5=$A21),SUMIF($A$10:$A$14,INDEX($C$2:$E$5,0,MATCH(
B$20,$C$1:$E$1,0)),$B$10:$B$14))

Hope this helps!

 

[Guest]

Hi,
I haven't been able to work out a formula-based solution but I have
a simple UDF-based solution. If this is of interest, let me have an e-mail
address (or contact me at toppers<at>johntopley.fsnet.co.uk) and I'll forward
to you.

 

[Guest]

Domenic came up trumps!

Hi,
I haven't been able to work out a formula-based solution but I have
a simple UDF-based solution. If this is of interest, let me have an e-mail
address (or contact me at toppers<at>johntopley.fsnet.co.uk) and I'll forward
to you.

 

[ronlim]

Thanks Domenic, it work great... except for one weird glitch.

One of the Day Shift markers I used is "*D" (in the example I gave D1)
Somehow any cell marked *D counts as 2 days. All my other markers *D1
*D2, etc work fine except *D

I've checked my legend, and it is marked as 1 day. I've tried the valu
0 (zero) in the legend, and it still gives me a 1. I can't seem t
figure out where that extra day comes from

 

[Domenic]

That's probably because your legend contains a shift marker where D is
the last character in the text string. An asterisk is considered a
wildcard character. So is a question mark. So *D will match xD, xxD,
xxxD, etc. Try using characters other than an asterisk or question
mark...