Formula help : COUNTIFS / SUMPRODUCT

[jm80ny]

I'm making a table to show different groups of workers, which day they work, when they get tested and how many workers and tests per day, as well as totals for the whole grid.

The YELLOW cells are where the formulas would go.

Each Row represents a work day.

Each Column represents a group of workers. In the Row 2 there will be quantities of workers in each group.

The legend explains the abbreviations, but W means work and R,E,T are all different types of tests.

On Day 1 there 8 people working (3x Charlie + 4x Edward + 1x Franklin). There are 7 people getting tests (4x Apple + 3x Charlie). I've manually filled in the cells in the example above.

Also the W10 are 10 hour work days. The W11s are 11 hour work days. on the bottom I want to know How many 10 hour and 11 hour days for the whole week.

Same thing for testing all week.

I was playing with COUNTIFS and SUMPRODUCTS, but haven't figured out an elegant solution. I think if someone helps me in the right direction to figure out the daily subtotals, I'll be on my way to getting the rest.

Anybody have any ideas? Does this make sense?

[bebo021999]

Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[jm80ny]

Thank you! I just did.

[bebo021999]

Does F7=3 (not 2) (K7=T and K2=3)
F9=5 (not 3) (J9=T and K9=R and 2+3=5)

???

[jm80ny]

Yes! Sorry, very tired over here. This is why I need formulas. I'll fix the post now.

[bebo021999]

Try in F5:

=SUMPRODUCT((ISNUMBER(SEARCH("R",$I5:$N5))+ISNUMBER(SEARCH("E",$I5:$N5))+ISNUMBER(SEARCH("T",$I5:$N5)))*$I$2:$N$2)

G5

=SUMPRODUCT(ISNUMBER(SEARCH("W",$I5:$N5))*$I$2:$N$2)

K10

=SUMPRODUCT(ISNUMBER(SEARCH("W10",$I$5:$N$9))*$I$2:$N$2)

N10

=SUMPRODUCT(ISNUMBER(SEARCH("W11",$I$5:$N$9))*$I$2:$N$2)

[jm80ny]

OMG!!!! That is magical! Thank you Sooooo much!