Excel COUNTIF with multiple criteria and both row and column

Question

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?

Answer 1

Count Cells Based On Text Value Using Multiple Criteria
For example, I have the following data, which includes several products, and I need to count the number of KTE and KTO values in the same column.

Please use the following formula to calculate the number of KTE and KTO:
=COUNTIF($A$2:$A$15,"KTE")
+COUNTIF($A$2:$A$15,"KTO")
Then press the Enter key to get the total of these two goods' numbers.

Notes:

1. The data range you want to use is A2:A15, and the criteria you want to count are KTE and KTO in the calculation above.

2. If you wish to count more than two criteria in a column, simply use =COUNTIF(range1, criteria1) + COUNTIF(range2, criteria2) + COUNTIF(range3, criteria3)+...