Forum Discussion
COUNTIF with Criteria
Hi
I am trying to count how many rows in the "C" column with different criteria that are listed in "P" column and set as "Wave-4".
The criteria in "C" are Sybase","MariaDB","MongoDB","Oracle","SQL"
This formula works for one criteria, but I cannot figure out how to use multi criteria to look for those listed above.
Works: =COUNTIFS(PlanningList!$C$2:$C$1792,"Sybase",PlanningList!$P$2:$P$1792,"Wave-4")
Fails: =COUNTIFS(PlanningList!$C$2:$C$1792,"Sybase","MariaDB","MongoDB","Oracle","SQL",PlanningList!$P$2:$P$1792,"Wave-4")
Any help would be appreciated 🙂
5 Replies
- You answered your own question. If you want This AND That, you must repeat the criteria_range for every criteria. Which is tiring. You can attack it with LET, as in
=LET(planList;PlanningList!$C$2:$C$1792;COUNTIFS(planList,"Sybase",planList,"MariaDB", ...
That's as good as it gets, I suppose. If you want This OR That, it gets harder. You could go via FILTER in both cases, is in
=LET(planList;PlanningList!$C$2:$C$1792;COUNT(FILTER(planList;(planList="Sybase) + (planList="MariaDB") + ...
Knock yourself out. Use + for OR and * for And.- Thank you for your responses. The other solution allowed me to use the same function, so I went with that.
Thanks again.
Try the formula below - put the list of conditions in curly braces to make an array, and then wrap the whole thing in a SUM unless you want the count for each condition to be spilled into adjacent cells in the same row.
=SUM(COUNTIFS(PlanningList!$C$2:$C$1792,{"Sybase","MariaDB","MongoDB","Oracle","SQL"},PlanningList!$P$2:$P$1792,"Wave-4"))
=SUMPRODUCT(COUNTIFS(PlanningList!$C$2:$C$1792,{"Sybase","MariaDB","MongoDB","Oracle","SQL"},PlanningList!$P$2:$P$1792,"Wave-4"))
This formula works in my Excel 2013 sheet.
