+ Post New Thread
Page 2 of 2 FirstFirst 12
Results 16 to 18 of 18
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. #16

    Join Date
    Jan 2011
    Posts
    7
    Thank Post
    5
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    Hi

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

  2. #17

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,432
    Thank Post
    1,432
    Thanked 1,160 Times in 794 Posts
    Rep Power
    705
    Quote Originally Posted by Matth View Post
    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. #18

    Join Date
    Jan 2011
    Posts
    7
    Thank Post
    5
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    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 FirstFirst 12

Similar Threads

  1. Joomla Blog not showing 2 columns correctly
    By Nick_Parker in forum Web Development
    Replies: 4
    Last Post: 12th August 2010, 05:35 PM
  2. [Pics] Why men should not write advice columns.
    By mattx in forum Jokes/Interweb Things
    Replies: 1
    Last Post: 14th March 2010, 06:14 PM
  3. outlook 2007 calendar add columns
    By imiddleton25 in forum Windows
    Replies: 0
    Last Post: 13th January 2010, 11:54 AM
  4. Layout problem when using 2 columns
    By SimpleSi in forum EduGeek Joomla 1.0 Package
    Replies: 3
    Last Post: 17th May 2008, 09:19 AM
  5. Web query in Excel
    By pinemarten in forum How do you do....it?
    Replies: 2
    Last Post: 20th January 2006, 09:22 AM

Thread Information

Users Browsing this Thread

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

Tags for this Thread

Posting Permissions

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