Search code examples
averagelookupsumifs

Look up an account then average associated values excluding zeros

On one sheet, I have account code and in the cell next to it, I need to look up the account code on the next sheet to average the cost excluding those cells that are zero in col. b from the average calculation.

The answer for London should be: £496.33 but having tried various sumifs / countifs I cannot get it to work.

enter image description here

enter image description here

enter image description here

Solution

  • You probably need COUNTIFS which -- similar to the SUMIFS you are already using -- allows to define multiple critera and ranges. So, if the column R contains the values, you want to build the average upon, and the column H in the respective row must equal $B$28 to be included in the sum, the respective COUNTIFS looks as follows

    =SUMIFS('ESL Info'!$R:$R,'ESL Info'!H:H,$B$28)/COUNTIFS('ESL Info'!$H:$H,$B$28, 'ESL Info'!$R:$R, "<>0")

    ie additionally to the value in the H-column to equal B28 it also requires the value R-column (ie the actual number you are summing up) to be different from 0

    You could also add the same criteria 'ESL Info'!$R:$R, "<>0" to your SUMIFS, but that isn't necessary, because a 0 doesn't provide anything to you sum, thus it doesn't matter if it's included in the sum or not ...

    And depending on the Excel version you are using, you may even have the AVERAGEIFS function available, which does exactly what you want

    =AVERAGEIFS('ESL Info'!$R:$R,'ESL Info'!$H:$H;$B$28,'ESL Info'!$R:$R,"<>0")