Countif does not work properly

[Excelquestion35]

Hi all,

For some reason I can not find the fix for a an easy Countifs function.

=COUNTIFS($D13:$S13;">"&3;$D1:$T7;Overview!$B13)/VLOOKUP(B13;Table2[#All];2;FALSE)

leads to an error while below one is working fine as it should be, but this formula is not convenient to drag down since I have to adjust the row it looks for per department.

=COUNTIFS($D14:$S14;">"&3;$D$1:$T$1;Overview!$B14)/VLOOKUP(B14;Table2[#All];2;FALSE)

The Vlookup is just a number that counts the number of processes where the department is written in red above the code, so is not in scope for the issue.

 

[Peter_SSs]

I cannot see how the second formula works.
From the COUNTIFS Help ..

Each additional range must have the same number of rows and columns as the criteria_range1 argument.

.. yet your second formula above has one range going from D:S and another going from D:T. That is, a different number of columns.

You first formula is even more uneven. It has ..
- one range D:S x 1 row
- one range D:T x 7 rows
That is, different number of columns and different number of rows.

BTW, if you want to bold, use the RICH code tags, not the VBA tags.

 

[Excelquestion35]

Thank you for your reply and info.

I am actually looking for a way in which it counts within the 7 rows instead of the first row. To elaborate a bit more on what I want to achieve; I want to count the number of processes (the columns) in which the value is higher than 3 and divide that by the amount of processes per department. The formula is now build this way that I manually check in which the row for which it counts is manually adjusted. D2S department must be view in the first row, while when the department S2F - 5 Shift must be counted based on the third row values.

Currently the formula is not easy to manage; as soon as the department changes, the formula must manually be adjusted as well.
That's why I inserted the range of 7 rows (D1:T7), but then the formula is not working any longer. Looks like it is not going to work with the countif function based on the description then.
Do you know if there is another way to do this by any chance?

 

[Peter_SSs]

What about this COUNTIFS?

=COUNTIFS($D14:$S14,">"&3,INDEX($D$1:$S$7,MATCH(Overview!$B14,$C$1:$C$7,0),0),"?*")/VLOOKUP(B14,Table2[#All],2,FALSE)

 

[Excelquestion35]

Thank you, Peter, it works!

 

[Peter_SSs]

You're welcome. Thanks for the confirmation.