I am trying to get numbers to display how many cells have data in them out of a a total amount of cells eg. 10 cells have data, out of a total 15 cells, so display as 10/15 and have it update as the number of cells with data changes.
MacBook Pro (2020 and later)
One way you can do this in Numbers is like this:
=COUNTIF(C2:N2,"Pass")&" / "&COUNTIF(C2:N2,REGEX("."))
The first COUNTIF counts how many cells are in the range that have the value 'Pass'.
The second COUNTIF counts how many cells are in the range that have anything in them (are not "blank"). The REGEX matches any character.
Substitute ; for , in the formula if your regions uses , as a decimal separator.
SG
Do the total number and position of cells stay constant?
Is the data in the 'cells with data' numeric, quasi numeric, or text?
If the answers are yes, yes and numeric, you can use COUNT to count the number of cells with data, and display that number /15 (as text, to prevent Numbers from doing the division).
More detail regarding the type of data in the cells and the location(s) of the cells within the table could lead to a more detailed and specific answer.
Regards,
Barry
Hi Xanthir,
More information, please!
I do see a possible solution. However, I am having trouble visualising the layout of your table. Please post a screen shot of a relevant part of your table so that we can see what you see.
Regards,
Ian.
Sorry, I was not at a computer when I wrote the first reply.
This is the original data in excel, with the formula "=AZ27&" / "&BC27"
I am trying to replicate this into Numbers with the same layout, but going the opposite direction (L to. R instead of R to L). The range of the 12 cells will remain constant, but the total number of cells will increase monthly from 1 to 12.
Thanks for the reply.
The basic idea is that the data assesses monthly whether someone meets certain requirements or not and then compares how many months they pass compared to how many months have elapsed.
There would always be a range of 12 cells, and the data in each range would have uniform data sets (numerical or textual) however not each set will be the same.
So ideally the final display will show 1/1 for the first month of the pass, 2/2 for the second month 4/6 if they fail two months at the 6 month mark etc.
How to display x out of y cells
