# Microsoft Office Application Help - Excel Help forum > Excel Formulas & Functions >  >  Excel 2007 : Sum divided by count

## dancing-shadow

Hi, 

I have various tables of data on one sheet which I'm trying to add up, and average in a top main summary table. 

I've managed to use =SUM(D94+D180+D266+D352+D438+D524) for the total, but then I want to divide that total but the amount of cells populated with a number. So this could be anything from 0 if none of the cells have a number, to 6 if all of them do). 

The problem I have, is all those cells have a formula showing the sum of other numbers. Excel refuses to ignore the formula. So despite maybe only 4 cells having an actual number (and therefore I only want to divide by 4), it includes the formula, so divides the total by 6 all the time. 

How can I make it count the number of actual numbers, and ignore cells with formula that don't produce any number?!

----------


## NBVC

Try:

=SUM(D94,D180,D266,D352,D438,D524)/COUNT(D94,D180,D266,D352,D438,D524)

----------


## dancing-shadow

> Try:
> 
> =SUM(D94,D180,D266,D352,D438,D524)/COUNT(D94,D180,D266,D352,D438,D524)



Yep, that was my first line of thought, but as those cells all have formula in them, excel counts it whether the formula has provided a number or not. 

EG. cell D94 has the formula =D24+D34+D44+D54+D64+D74+D84

All of these cells are empty however, so therefore D94 is technically blank. The same goes for the other 5 cells - they all have formula totalling numbers in rows above them.

As there is a formula in it though, excel is counting it when I don't want it to.

----------


## NBVC

Notice that I changed your summation formula to SUM(arg1,ar2,ar3).... this ignores blanks (and formula blanks too).

If your formulas are returning numbers or blanks (with "") then my formula should work.

----------


## dancing-shadow

Apologies, I've just realised the formulas are returning a value of '0', just formatted with white text, so the cell looks blank... (I guess that's what happens when I didn't create the sheet in the first place *mutter*)

So, I now need to ignore any cell that = 0. I've tried adding =SUM(D94,D180,D266,D352,D438,D524)/COUNT((D94,D180,D266,D352,D438,D524)*,"=0"))*

onto it, but this returns a #value! error....

----------


## NBVC

And I guess you can't change the original formula in those cells to return a blank instead of 0?

----------


## NBVC

If not then perhaps try:

=SUM(D94,D180,D266,D352,D438,D524)/SUM(COUNTIF(INDIRECT({"D94","D180","D266","D352","D438","D524"}),"<>0"))

----------


## dancing-shadow

I can't make the other formula give a "" value if it equals 0, as something else that tries summing it then produces an error because "" isn't a number. So yes, it has to stay as a 0, but not be counted in the formula I'm trying desperately to get.

The formula you gave does seem to work, but I can't drag it, the 2nd half of the bits after 'INDIRECT' in the "'s will not change - I need to drag it down 4 rows, and accross about 40... I was hoping for some way that didn't involve editing 40 formulae to make it work  :Smilie:

----------


## NBVC

Does this work for you then?




```
Please Login or Register  to view this content.
```


*must be confirmed with CTRL+SHIFT+ENTER* not just ENTER.

----------


## adnan.ahmad23

This will work.

=SUM(E6:E36)/(COUNT(E6:E36)-COUNTIF(E6:E36,"0"))

Note: Change your range as required.

----------


## martindwilson

well if op comes back after nearly 2 years!

----------


## adnan.ahmad23

For Posterity & Beyond!  :Smilie:

----------


## DaddyDano

Might be late answer but I can tell you there is a man in Indiana on 9-17-2013 that is happy you answered this question. I even registered just so I can thank you.

Thanks!

Have a great day!

----------


## william johnson

One the teachers at my son's school had asked for help with this very same topic...  this forum has proven its value so very much!  Thank you

----------


## martindwilson

well if =SUM(E6:E36)/(COUNT(E6:E36)-COUNTIF(E6:E36,"0")) was the answer to this thread it would have been posted much earlier 
the op has a
dis-contiguous range
D94,D180,D266,D352,D438,D524
you can't count/countif on that directly

----------


## adnan.ahmad23

For a dis-contiguous range, use this

e.g

=SUM(E9+E12+E18+E21+E24+E31+E38)/(COUNT(E9,E12,E18,E21,E24,E31,E38)-(COUNTIF(E9,0)+COUNTIF(E12,0)+COUNTIF(E18,0)+COUNTIF(E21,0)+COUNTIF(E24,0)+COUNTIF(E31,0)+COUNTIF(E38,0)))

----------

