# Percentage and averaging with multiple sections in Excel worksheet

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?

-

## 2 Answers

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)
``````
-

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.

-