Office Software Thread, EXCEL Query - Finding the Average if two columns meet a need. in Technical; Hi Thanks for the formula, however, I have Excel 2003 and SUMIFS and COUNTIFS are not supported!...
1. Hi

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

2. 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```

3. ## Thanks to LosOjos from:

Matth (28th January 2011)

4. Hi

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

Thanks again

SHARE:
+ Post New Thread
Page 2 of 2 First 12

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•