Countifs function: two criteria in the same range for text

Question

I am using the countif function to count only those items achieving a certain criteria. My problem is I have a certain range wherein there are two items (criteria) I want to include in my logic so that those two items will be included in the count. Let us take for example I want to count in a range that should meet "Water" and "Gas" altogether.

Thank you!

I had added details for better illustration purposes. Here's my table of data:

Table of Data

My criteria would be as follows:

I only want to include the following items meeting a series of criteria, arranged in order of priority:

1. Priority 1: Column "Company": Items under Company "A"
2. Priority 2: Column "Status": Items under "On-Going" and "Completed"
3. Priority 3: Column "Type of Project": Items under "Program Development"
4. Priority 4: Column "Application": Items under SAP and HRIS application

Based on my own analysis without using excel formulas, I would come up a total of 3 items: Item No. 1, Item No. 3, and Item No. 4, that met the criteria set. Now I want to do this using the excel logic.

Thanks again for the help.

Answer 1

Maybe add two Countif() statements?

Answer 2

If you just want to count the cells that contain "Water" or "Gas", and you aren't committed to using COUNTIF() of COUNTIFS(), you can do it with SUM(), like this:

=SUM((A1:A6="Water")+(A1:A6="Gas"))

This is an array formula, so you must type Ctrl+Shift+Enter after typing the formula.  The (A1:A6=value) subexpressions are Boolean arrays; they evaluate to TRUE where the cell equals the value and FALSE where it does not.  And when you use Boolean (TRUE or FALSE) values in a numeric context, like something + something, the TRUE values are treated as 1, and FALSE is treated as 0, so the sum of these numbers is a count.

Answer 3

You can do it with SUM like mentioned above:

=SUM((C1:C10="A")*(OR(E1:E10="Ongoing";E1:E10="Completed"))*(F1:F10="Program Development")*(OR(D1:D10="SAP";D1:D10="HRIS")))

And then press ctrl+shift+enter when you enter the formula.

Answer 4

You can use this Formula if you need countifs:

=SUM(COUNTIFS(C2:C13,"A",D2:D13,{"SAP","HRIS"},E2:E13,{"On-going";"Completed"},F2:F13,"Program Development"))

Where C2:C13 is the column Company
E2:E13 the Status
F2:F13 the Type of Project
D2:D13 the Application