Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a document that is divided in seven sections, each one containing different amounts of lines. It was created to keep count of how many cells are filled out (work completed) in each section, and then to calculate the percentage for each section and finally the percentage for the whole worksheet.

The sections have as calculation the following example:

=COUNTA(F181:F263)/(COUNTA(F181:F263)+COUNTBLANK(F181:F263))

The last cell in the column uses the following:

=(F9+F56+F64+F108+F128)/5

which treats each section as having the same number of cells, which is not the case.

Is there a way, in the last cell, to calculate the percentage for all cells in the worksheet using the subcalculations by section?

share|improve this question
add comment

2 Answers

Your 'standard' formula should work with just the addition of appropriate values for x and y:

=(COUNTA(F2:F265)-x)/(COUNTA(F2:F265)+COUNTBLANK(F2:F265)-y)

where x would probably be 5 or 7 (depending upon how many section percentages occupy cells in column F above the 'last', for the formula above, cell [you mention 7 but divide by 5])

and y would be the count of any blank cells in the same range that do not represent work not completed - so might be 7 if you have a blank row between each section percentage and the next section, or the formula above.

F2: assumes data starts in Row2 (to allow for column labels) and Row265 is assumed to be the first blank row below all the data.

share|improve this answer
add comment

You could get the correct counts by storing the calculations in two fields for each section one for the filled in portion (COUNTA) and one for the total (COUNTA + COUNTBLANK).

In your example you could place the filled in values in F and totals in G

So your final cell would look like

=(F9+F56+F64+F108+F128)/(G9+G56+G64+G108+G128)
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.