Excel COUNTIF with multiple criteria and both row and column

user_15162600 0
Viewed 214

I'm trying to make a COUNTIF that checks range E8:AI8 for the letter "D" and the range E7:AI7 for any of the following numbers 2;4;8;9;15;20;26;27;33;34;40;41

Also cells in range E5:AI5 to not equal any cell in C105:C118

  • E5:AI5 is dates in a calender.
  • C105:C118 is a list of dates for holidays.

My formular so far is looking like this:

    Sumproduct(countifs($E$8:$AI$8;"*D*";$E$7:$AI$7;
    {2;4;8;9;15;20;26;27;33;34;40;41};$E$5:$AI$5;"<>"&$C$105:$C$118))

However its not really turning out like i want it, the first two parts of the fomula are working as intended, but the last part comparing the row to the list of dates is not. Is it even possible to compare a row to a column? Or is there another way to do this?

countif excel-formula excel
1 Answers

That is not something you can do really inside a COUNTIFS() as described here. Consider to use SUMPRODUCT() with some MATCH() nested inside:

=SUMPRODUCT((E8:AI8="D")*(ISNUMBER(MATCH(E7:AI7,{2,4,8,9,15,20,26,27,33,34,40,41},0)))*(ISNA(MATCH(E5:AI5,C105:C118,0))))
user_97581940
Related