Excel COUNTIF with multiple criteria and both row and column

0 votes

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?

Mar 24 in Database by Edureka
• 13,640 points
367 views

1 answer to this question.

0 votes

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.

image
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.

image

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)+...

answered Mar 25 by gaurav
• 22,040 points

Related Questions In Database

0 votes
0 answers

How to get address, Column Name and Row Name of all marked rows in Excel table as rows in new worksheet

 need the row/column combinations marked with an ...READ MORE

Feb 24 in Database by Edureka
• 13,640 points
805 views
0 votes
1 answer
0 votes
1 answer

Excel 2003 - Match Column A with Column B & Column C to correspond to Column B

Write a standard IF formula that compares ...READ MORE

answered Mar 15 in Database by gaurav
• 22,040 points
810 views
0 votes
1 answer

Excel - Combine multiple columns into one column

In Excel, how can I concatenate (combine) ...READ MORE

answered Mar 30 in Database by gaurav
• 22,040 points
1,024 views
0 votes
1 answer

How to transform multiple tables in one excel sheet to one table with Power BI?

If my prediction is correct- You have a ...READ MORE

answered Apr 4 in Database by Edureka
• 13,640 points
931 views
0 votes
1 answer

EXCEL How to check if Column A contains specific value and Column B contains 2 specific values?

If you're looking for the string CHECK in cells ...READ MORE

answered Apr 6 in Database by gaurav
• 22,040 points
101 views
0 votes
1 answer

Split timestamp column into separate date and time columns in excel

Kutools for Excel allows you to split ...READ MORE

answered Apr 11 in Database by gaurav
• 22,040 points
921 views
0 votes
1 answer

How will I get all the rows from my table where name starts with 's' and ending with 'I'?

Hi Santanu, I understand your problem, You can try ...READ MORE

answered May 28, 2019 in Database by sampriti
• 1,120 points
780 views
0 votes
1 answer
0 votes
1 answer
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP