Numbers AutoSum

Question

Level 1

8 points

Numbers AutoSum

I created a spreadsheet for an attendance record for our sports group.

The row features the name of each member of a sports group the column features the date.

If a person attends that date I simply put numeral "1" in that column and the cell next to their name in the headers column I entered the sum adding all columns together (if they did not run that week then it's simply left blank) which calculates the total runs they have participated in.

However there is also another auto calculation I would like to add which is how many of those runs they they participated in, they set the trail (route). I thought of changing the font colour to red and then setting a sum to add all the red numeral "1"'s.

Is this possible?

Posted on Nov 7, 2021 5:58 PM

Reply

Answer 1

Badunit

Level 7

30,708 points

Nov 7, 2021 6:27 PM in response to Jaybay2

Functions have no access to the color or formatting of a cell so you cannot use those things in a formula.

One idea would be to use COUNTIF instead of SUM. In the columns with dates you would use a code for the things you are tracking. Participation might be a "P", set the route might be an "S", etc.

I can't see enough of your table to give you complete functions but they would look something like this:

Participation on row 6 would be of the form =COUNTIF(Q6:V6,"<>")

Route setting on row 6 would be of the form =COUNTIF(Q6:V6,"S")

I assumed "participation" includes "participated without setting the route" + "participated and set the route". It is counting all non-blank cells.

If you have a need for multiple codes in a cell, that can be handled by using the REGEX function in the COUNTIF. I won't get into that unless you need it.

Reply

Numbers AutoSum

Similar questions