Google Sheets calculate characters only once

user_13508247 0
Viewed 238

enter image description hereIs there a formula in google sheets to calculate a character only once. For example, if a row has 5 columns (Monday-Friday) and there are 2 or 3 columns marked with X. How can I calculate how many rows have an X. I don't need to know how many Xs there are just how many have an X?

google-sheets-formula
1 Answers

Reina, I have one answer, though there may be better ones.

This formula, pasted into B34, should do what you want. It merges all the cells in column B to F, in each row, into one value, substitutes out possible spaces, then checks if it has at least one "y" (as used in your example.

=COUNTIF(ARRAYFORMULA(
           SUBSTITUTE(B4:B29&C4:C29&D4:D29&E4:E29&F4:F29," ","")),
        "*y*")

It is coded to search all student rows, ie. between 4 and 29 - change these row numbers if necessary.

If the attendance might be marked with something other than a "y", you could change the "y" part of the formula to "?*". I just didn't know if other values might be used, eg. an "S' for sick day or something, and you wanted to ignore those.

Then, you can drag the new formula from B34, sideways on row 34, to G34 and beyond, and it should calculate the results for the subsequent weeks. It will shift the columns being checked by the formula automatically.

Let me know if this works for you, or if you need something else.

To possibly ease data entry, here is a sample sheet with the formula, but with check boxes replacing the cells where attendance is marked. https://docs.google.com/spreadsheets/d/1ON5Rc55aLVq_LHtFOfpgmf876bYg2ITfwpbifklr3lU/edit?usp=sharing

Here the formula is slightly modified to look for "TRUE" values, instead of "y"s.

UPDATE: To look for ANY non-blank cell in that range, and count "1" for every student that week that attended at least one day, the formula is:

=COUNTIF(
    ARRAYFORMULA( B4:B29&C4:C29&D4:D29&E4:E29&F4:F29), ">""")
or
=COUNTIF(
    ARRAYFORMULA( B4:B29&C4:C29&D4:D29&E4:E29&F4:F29), "?*")

See sample here: https://docs.google.com/spreadsheets/d/1ON5Rc55aLVq_LHtFOfpgmf876bYg2ITfwpbifklr3lU/edit#gid=461771088&range=B34:F34

Let me know if this answers your question, or do you need to do something specific with the "y,x, and o"s?

user_95938950
Related