COUNTIF formula with multiple criteria and table

Question

I have a list of four developers (A1="Dev1", A2="Dev2", A3="Dev3" and A4="Dev3").

Within a working week calendar, I have 5 columns, one for each day of the week (C1="Monday", D1="Tuesday", E1="Wednesday", F1="Thursday, G1="Friday").

If developer Dev3 has a day off on Tuesday, I'd go to D2 and input: "Dev3".

On H2, I have the following formula:

=COUNTIF(C2:G2,"*Dev1*")+COUNTIF(C2:G2,"*Dev2*")+COUNTIF(C2:G2,"*Dev3*")+COUNTIF(C2:G2,"*Dev4*")

In the scenario above, I'd have the value of H2 being '1'. If I edit D2 cell to something like this: "Dev3,Dev4", the result of H2 would be '2'.

This formula works well for what I need but I know that there is a more elegant way that I could use the list of the developers on A:A column, instead of creating a single COUNTIF element per developer.

Could anyone help me achieving the usage of the list A:A instead of creating a single COUNTIF element for every single developer, instead?

sheet's screenshot:

Would it be acceptable for you to collect the results in the column B besides each developer? in this case you can easily use this formula for each row: =COUNTIF(C$2:G$2,""&A1&"") — Michele
@Michele, good point, however I need the totals based on the week, instead. If I collect the results per developer, I would not be able to track the results for the week but for each developer individually, not knowing the outcome of their absence in a given week period. — fmagosso
Ok, now it is clearer to me what you would like to do. If your format is always the same (developer is a string of 4 characters and in case of multiple developers in the same cell they are divided by a comma you may apply this formula at the end of your calendar: {=SUM(LEN(SUBSTITUTE(C2:G2,",",""))/4)} — Michele
Hi @Michele, I'm trying to append the formula you've mentioned however nothing happens. The formula is just showing up in the cell, even after I press CTRL + SHIFT + ENTER. Am I doing anything wrong here? — fmagosso
I tested the formula and it works on my computer. Then I pasted it here. What do you see exactly in your screen? Could it be the cell that has a text format? — Michele

Unknown Unknown · Accepted Answer · 2015-12-24T13:38:50

Either a SUM/COUNTIF function array¹ formula or a SUMPRODUCT function should be able to count correctly providing there are no 'false positives' like Dev1 found in Dev12.

'array formula
=SUM(COUNTIF(C2:G2, "*"&A$1:INDEX(A:A, MATCH("zzz",A:A ))&"*"))
'SUMPRODUCT
=SUMPRODUCT(--ISNUMBER(SEARCH(A$1:INDEX(A:A, MATCH("zzz",A:A )), C2:G2)))

Note that in both cases, the list of developers from column A has been cut down to the minimum number of cells with,

A$1:INDEX(A:A, MATCH("zzz",A:A ))

¹ _{Array formulas need to be finalized with Ctrl+Shift+Enter↵. Once entered into the first cell correctly, they can be filled or copied down or right just like any other formula. Try and reduce your full-column references to ranges more closely representing the extents of your actual data. Array formulas chew up calculation cycles logarithmically so it is good practise to narrow the referenced ranges to a minimum. See Guidelines and examples of array formulas for more information.}

COUNTIF formula with multiple criteria and table

1 Answers