[Solved] Combine a VLOOKUP with a COUNTIF function

[paullystew]

I have a staff scheduling spreadsheet that I want to have a sub total of staff on, within a range of shifts and within a department query.
I tried using COUNTIF() to to total a few shift choices within a range, but only if the query selection matches the cell three columns to the left.

The issue cells are B4:J5

This gives me the total matches:
=COUNTIF(D$7:D$52;$L$72)+COUNTIF(D$7:D$52;$L$73)+COUNTIF(D$7:D$52;$L$74)
But I need it to only do the COUNTIF() on the cells that match the VLOOKUP()

=VLOOKUP($A$2;$A$14:$J$66;4;0)

So if you select "ACOUSTIC GUITARS" it will count how many "DAY SHIFT" staff are on.

How do I get it to have a multiple criteria sort for a function instead of just a permission switch to count the entire column?

[RusselB]

The COUNTIFS functions is mostly undocumented, but when you enter the function there should be a balloon prompt showing each stage.
This function has a slightly different format to the COUNTIF function, due to the fact that multiple conditions can be applied.

[paullystew]

I tried that and I couldn't get it to work until you pointed out about the formatting difference. I had missed that.
So my function now works albeit very clunky and not as eloquent as I would like it to be.

=COUNTIFS($A$16:$A$54;$B$7;D$16:D$54;$L$73)+COUNTIFS($A$16:$A$54;$B$7;D$16:D$54;$L$74)+COUNTIFS($A$16:$A$54;$B$7;D$16:D$54;$L$75)+COUNTIFS($A$16:$A$54;$B$7;D$16:D$54;$L$76)+COUNTIFS($A$16:$A$54;$B$7;D$16:D$54;$L$77)

Thanks!!

[RusselB]

You might be smart to put the COUNTIFS functions into multiple cells, using rows or columns that aren't (and likely won't) be used... Eg: Use column AAA
Then use the SUM function to add up the results in those cells.