# EXCEL Query - Finding the Average if two columns meet a need.

Show 25 post(s) from this thread on one page
Page 2 of 2 First 12
• 28th January 2011, 10:02 AM
Matth
Hi

Thanks for the formula, however, I have Excel 2003 and SUMIFS and COUNTIFS are not supported!
• 28th January 2011, 10:23 AM
LosOjos
Quote:

Originally Posted by Matth
Hi

Thanks for the formula, however, I have Excel 2003 and SUMIFS and COUNTIFS are not supported!

In that case, you're going to have to break it down in to chunks. I haven't got Excel 2003 here, but try this:

Code:

```You'll need to add this formula in to column 'E' in the example, and drag it down so that it calculates on each row: =IF(IF(A1="FAB",COUNTIF(B1,">0"),0),C1,0) That will check that each row meets your criteria and if it does, it will put the value of the row in to the cell, if not, it will put 0. Then, to find your average, use this formula: =SUM(E1:E6)/COUNTIF(E1:E6,">0") From the example I shared earlier, your sheet would now look something like this (with the average at the bottom of cell 'E'): FAB        123        564                564         154        326                0 FAB                985                0         156        125                0 FAB        145        325                325 FAB                125                0                                 444.5```
• 28th January 2011, 02:58 PM
Matth
Hi

Thanks to EVERYONE that contributed - very much appreciated.
The last example and formula work perfectly.

Thanks again
Show 25 post(s) from this thread on one page
Page 2 of 2 First 12