0

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:

   OP sample data image

fmagosso
  • 3
  • 4
  • 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 Dec 24 '15 at 12:05
  • @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 Dec 24 '15 at 12:12
  • 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 Dec 24 '15 at 12:29
  • 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 Dec 24 '15 at 13:27
  • 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 Dec 24 '15 at 13:34
  • Thank you for your help, @Michele! – fmagosso Jan 06 '16 at 17:36

1 Answers1

3

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

    Dev1_Dev2

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

  • Thank you for your help @Jeeped! This is exactly what I was looking for. Much appreciated! – fmagosso Jan 06 '16 at 17:38
  • I like the use of `A$1:INDEX(A:A, MATCH("zzz",A:A ))` to improve efficiency. However, something to note is that this will break when you get to Dev10. If you have Dev1 and Dev10 in two cells on a single row, it will return 3 instead of 2 because it is seeing "Dev1" within the "Dev10" string. Might not be an issue for the OP but just thought I'd point it out. – Gravitate Jan 10 '23 at 17:22