multiple lookups/matches to sum product

Testy1.xlsx
Hi everyone,
I'm trying to perform several several look ups to narrow down the data, then sum product what is left.
As ever i've hit a brick wall and am hoping for some help from the experts!

I'm looking to pull back the sum of all 'FTE' - 'Due In' columns in January only.
I will then be using the same principal for 'Sickness' and 'Holiday' values.

31-Dec 01-Jan 02-Jan
MTD Jan STAFF FTE Hrs FTE Hrs FTE Hrs
VALUE Due In 21 168 0 0 18 144
Sickness 1 8 0 0 0 0
Holiday 2 16 0 0 4 32

'VALUE' = the SUM of FTE columns for January only...

Then if i can get my head round that i want the 'Jan' cell to be a drop-down so the user can select the total of whichever month they want.

[server will not allow me to upload a test version yet]

Thank you as always!!

Hi smith_ts

See the attached using the SUMPRODUCT in the cells B19:E26.

Thanks Kevin.

That works a dream for the FTE columns for any month on my drop-down menu!
I've put yours into the red area (total1,2 & 3) on the testy doc.

I thought i could copy the formula and use "Hrs" to get the same results (total 3,4,5) but it does not work.
Have i missed something?

SUMPRODUCT((TEXT($F$3:$DE$3,"mmm")=$B$4)*($F$4:$DE$4="Hrs")*($E$5:$E$7=$E5)*($F$5:$DE$7))

I should say I only mention SUMPRODUCT because i learned its use recently, I'm happy to use anything that works. I'll just need to learn about it first.

Also, sorry about the merged cells, i'm not a huge fan either but it helps with cosmetic looks and sometimes that's what others demand!

EDIT : Just saw that those bloody merged cells are the reason it does not work. Put the date into both cells seperately and it works again. YOU WERE RIGHT, I HATE THEM!!!

Hi smith_ts

Thanks for the feed back.
Use center across selection instead of merge & center, in format cells > Alignment > Horizontal > Center across selection.