Formula Help

[gillinglis]

Need a YTD % total so add 4 amounts from 4 different sheets, then divide by the count. But some figures are 0 so don't want to include. Can't get Count or CountIf to do this. Any ideas?

Cheers.

 

[Joe4]

You can exclude zeroes from your COUNT with something like:
=COUNTIF(A1:A4,"<>0")

Zeroes won't hurt the adding/summing part, as they are adding nothing to the total.

 

[gillinglis]

Still can't get it to work. Can you give me a sample formula? I have four cells on four separate sheets which I've added. This is the formula I used:
=('Sheet1'!M9+'Sheet 2'!M9+'Sheet3'!M9+'Sheet 4'!M9)

this gives me a total of 200.

I want to get a % total and 2 of the 4 figures in the count are 0's so I don't want them to be used.

 

[Joe4]

You can use 4 separate COUNTIFs and add them, though that probably isn't the most efficient use of COUNTIF.

If you are looking at the same cell on every sheet, I believe I have seen some nifty formulas that account for that, though I can't seem to locate them right now.

In the meantime, this may not be the most efficient way, but it should work:
=('Sheet1'!M9+'Sheet 2'!M9+'Sheet3'!M9+'Sheet 4'!M9) /(('Sheet1'!M9<>0)+('Sheet2'!M9<>0)+('Sheet3'!M9<>0)+('Sheet4'!M9<>0))