Excel search and match two cells in the same row for multiple rows

Question

I am attempting to accomplish a look-up in Excel and I can't seem to find anything about it online. Admittedly, I may not be searching for the answer in the right way. So if this is redundant and has already been answered I apologize upfront.

I'll attempt to describe my situation the best I can.

My dilemma is as follows,

Example

I have several columns and rows of data, each column contains information specific too that row's item (See Example). I am trying to search Column 1 for specific text, then for each row that contains that specific text in Column 1. Find specific text in Column 4 of that same row and if Column 4 matches specific text and both are true. Count the number of times those two items match.

Something like this (but I know this doesn't give me the results I am looking for):

      A      B     C      D
1  "Bread"             "Butter"
2  "Pasta"             "Sauce"
3  "Corn"              "Butter"
4  "Pasta"             "Cheese"
5  "Pasta"             "Sauce"

etc...

IF A1:A2000="Pasta" AND (in the exact same row that "Pasta" appears) IF D1:D2000="Sauce" count the number of times both are true

In this case it would only equal "2". That is it should only count the number of times the afore mentioned search finds.

Thanks in advance for any and all help anyone can provide.

Rich

Answer 1

You can accomplish this with =COUNTIFS():

=COUNTIFS(A1:A2000,"Pasta", D1:D2000,"Sauce")

COUNTIFS will take multiple criteria and count the rows where those criteria are true.

You can also use =SUMPRODUCTS if you have an older version of excel (2003 or earlier)

=SUMPRODUCTS((A1:A2000="Pasta")*(D1:D2000="Sauce") * 1)

This will do the exact same multiple criteria count.