Count/IF formula between multiple Worksheets

**shotgunpet** · 04-09-2011, 10:26 AM

I have a budget spreadsheet that my family is starting to use and I want to "tech" it up. I would like to make the budget amount for the month (for example April) be the last 3 months average of what I actually spent. The problem is that the last 3 months are zeros and I get an amount that is not what I am looking for (85 dollars spent this month and nothing for the last 3 brings back an average of 21.50 for example). Any help would be great!

**TMS** · 04-09-2011, 10:46 AM

Instead of using AVERAGE, use SUM/COUNTIF ... sum the values ($85) but COUNTIF non-zero values (1) which will give you a more realistic "average" of $85.

Regards

**shotgunpet** · 04-10-2011, 09:11 AM

can someone write this formula out for me as I am not getting it. I have never used the countif formula.

Bill

**Cutter** · 04-10-2011, 09:16 AM

The formula would look like:

=SUM(A1:A4)/COUNTIF(A1:A4,"<>0")

Here is help for COUNTIF()

http://www.excelfunctions.net/ExcelCountif.html

**shotgunpet** · 04-10-2011, 02:47 PM

Thanks for the help I will look at the link. I tried the formula but I am working across multiple worksheets in a single file so my formula looks like this:

=SUM(January!C12:April!C12)/COUNTIF(January!C12:April!C12,"<>0")

**Cutter** · 04-10-2011, 03:41 PM

That won't work - COUNTIF() doesn't like 3D ranges but you can do workarounds.

Try this:

=SUM(January:April!C12)/SUM(COUNTIF(January!C12,"<>0"),COUNTIF(February!C12,"<>0"),COUNTIF(March,"<>0"),COUNTIF(April!C12,"<>0"))

An easier solution would be to have a cell on each monthly sheet equate to a 1 if the C12 cell on that sheet <> 0. Then you could have SUM()/SUM().