Excel 2007 : SUMIFS Formula Not Working

**milliemoo** · 03-06-2012, 11:46 AM

Hello, I have written a formula to calculate the average values in column AE (based on several other conditions), as per the following:

=SUM(SUMIFS(AE:AE,AA:AA,"Arabica Shift",AB:AB,">0",AD:AD,">0",F:F,"No"))/SUM(COUNTIFS(AA:AA,"Arabica Shift",AB:AB,">0",AD:AD,">0",F:F,"No",D:D,"1"))

At the moment I am getting an error message but I can't spot my mistake....is anybody able to help, please? Do you need to know what all the other column references?

I am trying to calculate the average value in column AE if.... AA=Arabica Shift, AB>0, AD>0, F="No", D="1"

Many Thanks

**Andrew-R** · 03-06-2012, 11:51 AM

The two SUM formula are surplus to requirements, so perhaps start by taking them out...

=SUMIFS(AE:AE,AA:AA,"Arabica Shift",AB:AB,">0",AD:AD,">0",F:F,"No")/COUNTIFS(AA:AA,"Arabica Shift",AB:AB,">0",AD:AD,">0",F:F,"No",D:D,"1")

I also note your SUMIF isn't considering column D - is that what you intended?

Edited to add: If you told us what the error is then that would be useful.

**tigeravatar** · 03-06-2012, 11:52 AM

milliemoo,

If you can use sumifs, then why not just use averageifs?
=AVERAGEIFS(AE:AE,AA:AA,"Arabica Shift",AB:AB,">0",AD:AD,">0",F:F,"No",D:D,1)

**milliemoo** · 03-06-2012, 11:56 AM

Hello Andrew, Thank you for your prompt reply. Column D refers to the financial period and so in t his example, I only want it to consider those entries where column D = 1....have I missed this out?? I thought I was being very clever figuring this our but clearly not!

**milliemoo** · 03-06-2012, 12:01 PM

Tigeravater - that makes so much more sense and appears to work! Thank you very much.

**milliemoo** · 03-06-2012, 12:06 PM

Also, I want to use this formula in a seperate worksheet within the same workbook but when I paste it in and change the reference cells, it brings up an error....it looked like this....

=AVERAGEIFS('PIR Data'!AE:AE,'PIR Data'!AA:AA,"Arabica Shift",'PIR Data'!AB:AB,">0",'PIR Data'!AD:AD,">0",'PIR Data'!D:F,"No",'PIR Data'!D:D,1)