2

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?

Mohamed Elgazar
  • 68
  • 11
Reina
  • 21
  • 2
  • there are several ways of doing this - with a query, a filter, etc. If you shared a copy of your sheet, with test data only, it would help people give the best possible answer to your question. If possible, show exactly what text/data you'd like the resulting column(s) to have, and someone here will come up with a formula for you. – kirkg13 May 09 '20 at 23:47
  • I added a screenshot of the database I was given. You'll see 14 as a total because there are actually a lot more student on the list I just took the screenshot to reflect a few. Basically what I need is a total number of student's who attended class that week. I don't need a total number of times they attended. If the student only attended 1 day during the week it counts for the whole week. If the student attended 3 days it still counts as once for the whole week. Hope this helps. Thank you in advance. – Reina May 10 '20 at 06:11

1 Answers1

1

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?

kirkg13
  • 2,955
  • 1
  • 8
  • 12
  • It worked!! Thank you so much. I'll have to adjust it for other teachers but at least I now have this to work with. Hopefully I can figure it out for the others. THANK YOU SO MUCH! – Reina May 11 '20 at 17:51
  • How do I count multiple characters? I tried adding the extra characters to the end of the function but it didn't work. =COUNTIF(ARRAYFORMULA( SUBSTITUTE(B4:B29&C4:C29&D4:D29&E4:E29&F4:F29," ","")), "*y*", "*x*","*o*") – Reina May 11 '20 at 18:36
  • @Reina, can you be clearer on what you want to count? If there is a "y", or an "x", or an "o", should they all be treated the same, and just count once for that row? – kirkg13 May 11 '20 at 22:21
  • @Reina, I've added a second sheet to the sampe sheet, called Sheet1-x-y-o. Is that doing what you want? Any cell that is not blank is counted. The new formula is at the bottom of my answer, above. – kirkg13 May 11 '20 at 22:42
  • THANK YOU that works. What does ">""" mean? I'd like to learn how I can manipulate the formula to include other codes the teachers might be using. I was given 50 teacher google sheets and about half of them are using different coding. Some are entering zoom, GC, etc. I was able to use "?*" on a few but it gets tricky when they also include NA or NP (not participating). I don't want to count NAs or NPs or whatever other code their using for non-participant. Thank you for your help – Reina May 11 '20 at 23:01
  • @Reina, the ">""" is meant to mean not blank, or greater than blank. But I'm still reading up on the best way. There are other options, like "?*" , which means one or characters, any character. For your purposes, I think they should all work. It only gets tricky if someone puts a period, or a space in one of the cells. If interested, look at the different answers (and comments) for this question: https://stackoverflow.com/questions/12519073/count-cells-that-contain-any-text. If you feel you have an answer to your specific question, you can mark this as a correct answer. – kirkg13 May 11 '20 at 23:16
  • Can an array include this: (J$4:J21, "REMIND") (J$4:J21, "*zoom*") (J$4:J21, "*gmeet*") (J$4:J21, "GC"). This teacher is not using Xs or Ys. I currently have this is formula =COUNTIF(J$4:J21, "REMIND") + COUNTIF(J$4:J21, "*zoom*") + COUNTIF(J$4:J21, "*gmeet*") + COUNTIF(J$4:J21, "GC") to count each column. But I just need to know, out of the 5 five columns (Monday-Friday) how many have at least one of these (for the week)? – Reina May 12 '20 at 16:19
  • @Reina Yes, did you test it? The sample sheet I gave you seems to work fine for ANY non-blank entry in those columns, using "?*" as the test. See here: https://docs.google.com/spreadsheets/d/1ON5Rc55aLVq_LHtFOfpgmf876bYg2ITfwpbifklr3lU/edit#gid=461771088&range=I4 – kirkg13 May 12 '20 at 17:45
  • I want to actually enter "REMIND", "*zoom*", "*gmeet*", and "GC" into the formula. So I can change the characters to fit other teachers needs. I entered my data onto your google sheet. If I use ?* it counts NP or NA which I don't want. – Reina May 12 '20 at 18:56