Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!


Free Excel Forum

Sumif/averageif With Multiple Ranges

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

This is what i was using,


I want to add more sets of ranges and average ranges like this,

=AVERAGEIF(($B$6:$B$48,$B$70:$B$112,$B$134:$B$176,$B$198:$B$240),B262,($C$6:$C$48,$C$70:$C$112,$C$13 4:$C$176,$C$198:$C$240))

Which of course returns #VALUE!

Help Please!!!

View Answers     

Similar Excel Video Tutorials

Helpful Excel Macros

Delete Empty or 'Broken' Named Ranges (#REF!)
- This macro will delete named ranges which refer to empty or #REF! ranges. This means that if a named range has been 'br
List All Named Ranges in Excel - Displays the Name and Value for Every Named Range Within the Active Workbook in Excel
- List all of the named ranges in a workbook in Excel and the corresponding values stored within every named range. This
Delete Multiple Named Ranges Quickly
- This macro will allow you to delete multiple named ranges very quickly. When you run this macro, a message box will pop-
Pop-Up Message Box When a Range of Cells Reaches a Certain Average
- This macro will display a message box when the numbers within a range of cells reaches an average of 5. The current mac
Make Text to Uppercase
- This macro will change all text within the selected cells to uppercase. It works only on selected cells within Microsoft

Similar Topics

Hello everyone,

I'm a beginner with excel and have a question regarding the averageif function and whether or not I can use it in my specific case.

I basically need to satisfy 4 or 5 ranges and then average that information for an entire column if all 4 or 5 ranges are satisfied. Is there a way I can do this using averageif function?

More specifically I need to do this : =averageif(a1:a5 = "M" AND, b1:b5 = "SOUTH", AND c1:c5 = "STRUCTURE", AND d1:d5 >=08:00 BUT


I'm currently trying to figure out a way to pull an average from a large array of of cells and want to segragate average by certain terms. I've been very unsuccessful up to this point and hoping that someone here can help.

Here is my question posed at another forum:
"I'm trying to pull an average of a large selection of data in a couple different area's of worksheet. I would imagine I need to use the AVERAGEIF function, however I can get it to work. I'm tryinig to get the average rate for select ranges of terms. (see attached mock spreadsheet)

so say the term is 1-5 years, I'm trying to develop a function that will pull from all 3 tables the average rate for any loan with a term of 1 - 5 years. The same for 6-10 years & 11-15."

the formulas I tried were rather bloated, and of course did not work:

=AVERAGEIF(B4:B59,"11",C4:C59)+AVERAGEIF(B4:B59,"12",C4:C59)+AVERAGEIF(B4:B59,"13",C4:C59)+AVERAGEIF (B4:B59,"14",C4:C59)+AVERAGEIF(B4:B59,"15",C4:C59)+AVERAGEIF(B77:B93,"11",C77:C93)+AVERAGEIF(B77:B93 ,"12",C77:C93)+AVERAGEIF(B77:B93,"13",C77:C93)+AVERAGEIF(B77:B93,"14",C77:C93)+AVERAGEIF(B77:B93,"15 ",C77:C93)+AVERAGEIF(B111:B133,"11",C111:C133)+AVERAGEIF(B111:B133,"12",C111:C133)+AVERAGEIF(B111:B1 33,"13",C111:C133)+AVERAGEIF(B111:B133,"14",C111:C133)+AVERAGEIF(B111:B133,"15",C111:C133)

=AVERAGE(SUMIF(B4:B59,"11",C4:C59)+SUMIF(B4:B59,"12",C4:C59)+SUMIF(B4:B59,"13",C4:C59)+SUMIF(B4:B59, "14",C4:C59)+SUMIF(B4:B59,"15",C4:C59)+SUMIF(B77:B93,"11",C77:C93)+SUMIF(B77:B93,"12",C77:C93)+SUMIF (B77:B93,"13",C77:C93)+SUMIF(B77:B93,"14",C77:C93)+SUMIF(B77:B93,"15",C77:C93)+SUMIF(B111:B133,"11", C111:C133)+SUMIF(B111:B133,"12",C111:C133)+SUMIF(B111:B133,"13",C111:C133)+SUMIF(B111:B133,"14",C111 :C133)+SUMIF(B111:B133,"15",C111:C133))

Any help would be greatly appreciated. Thanks!

P.S. I'm not sure if there is a way for me to attach a spreadsheet here, if so please let me know.

You can select two ranges and have Excel Sum One Coloumn while looking in another column.

example: =SUMIF(A1:A30,"Time",S1:S30)

What would be the formula used if you wanted to AVERAGEIF? I am trying to average times.


Obviously Excel doesn't like AVERAGE IF.

Wow, my first post since March.

I am trying to use =averageif() to average a range, the problem is the range is actually multiple ranges that are not consitant.

I cannot use =averageif() because of that.

I know this won't work.


Problem with the AVERAGEIF() fcn. I wish to average if between a value, cannot find the correct syntax:

ie) =AVERAGEIF($I$7:$J$1011,"=1

This is definitely a simple question and i'm certain it's something that i'm overlooking in writing a formula, but say you have 5 values:

A1: 600
A2: 715
A3: 822
A4: 901
A5: 1001

B1: 600
B2: 950

I want to create an averageif formula. What i would like it do is to average if higher than 600, but lower than 950. So the formula i have is:

I used sumif in Col B to get the sum by category, "Averageif" in Col D to get the average by category. Using the same logic, what is the function for the minimum and maximum? Appreciate you help.


  A B C D E F G 3 Category Amount           4 Administrative Support        24.00           5 Administrative Support        26.00           6 ADP Hardware Specialist        55.00           7 ADP Hardware Specialist        56.00           8 Application Engineer        66.00           9 Application Engineer        70.00           10 Application Programmer        83.00           11 Application Programmer        82.00           12               13               14 Summary Sum if Averageif Minif ??? Maxif???     15 Administrative Support        50.00 25         16 ADP Hardware Specialist     111.00 55.5         17 Application Engineer     136.00 68         18 Application Engineer     136.00 68         19 Application Programmer     165.00 82.5         20               21 I used sumif in Col B to get the sum by category, "Averageif" in Col D to get the average by category.             22 Using the same logic, what is the function for the minimum and maximum?             23               24 Appreciate you help.             25               26              
Spreadsheet Formulas Cell Formula B15 =SUMIF($A$4:$A$11,A15,B$4:$B$10) C15 =AVERAGEIF($A$4:$A$11,A15,$B$4:$B$10) B16 =SUMIF($A$4:$A$11,A16,B$4:$B$10) C16 =AVERAGEIF($A$4:$A$11,A16,$B$4:$B$10) B17 =SUMIF($A$4:$A$11,A17,B$4:$B$10) C17 =AVERAGEIF($A$4:$A$11,A17,$B$4:$B$10) B18 =SUMIF($A$4:$A$11,A18,B$4:$B$10) C18 =AVERAGEIF($A$4:$A$11,A18,$B$4:$B$10) B19 =SUMIF($A$4:$A$11,A19,B$4:$B$10) C19 =AVERAGEIF($A$4:$A$11,A19,$B$4:$B$10)

Excel tables to the web >> Excel Jeanie HTML 4


I am trying to tidy up a spreadsheet so that it doesn't show #DIV/0! errors when there are empty cells in a range.

Here's where i am starting:


This will give me the answer I am looking for. However, in order to complete the spreadsheet before data is going in I want to put the formula in, this is giving me the #DIV/0! error.

So far I have tried the following:

=IF(ISERROR(M3:M12),"",(AVERAGEIF(M3:M12,">0",M3:M12))) - This will blank off the #DIV/0! error and everything else too

reports #value! as the result (I suspect that this has something to do with the averageif statement. I don't think i'm far off just need a little pointer...


Hi Excel Gods-

I currently have this equation:


Where L5 = 100

I'm interested in having the formula averageif B column value is between L5 & L6.

Where L6 = 200

So I want it to find all values in B$ that are between >= 100 but < 200, and then average their C$ counterparts.

What's the best way to do something like this?



I'm looking for formula that would give me an average based on criteria. I have found previous posts but non that seem to work for me. Basically what I want is something like this:

sumif(A:A,120,B:B) where B:B has blank cells, but instead of sumif, averageif.


I have created a formula for calculating an average for amounts in a column. I wish to be able to copy that formula down a column.

The formula I have used is =AVERAGEIF(K3:K4,"<>0")

When I copy this and paste it into the other cells in the column, the first cell reference does not "lock"

Ie. the result I want is =AVERAGEIF(K3:K5,"<>0")
etc in the consectutive cells running down the page.

But The result I get is =AVERAGEIF(K4:K5,"<>0")

Please, How do I copy such a formula??

I am trying to average multiple cells, only if they are greater than zero.

I tried

=AVERAGEIF((A2,D2,F2,H2),">0")) but for some reason it's not working.

Any help?

Averageif returns two different values!
In cells G131:R131, I have the following values...
-.17, 23.03, 16.52, 41.12, 36.87, 55.55, -.74, -30.28, -2.29, -6.25, 175.00, 175.00.

If I use average for the first 10 numbers, I get 13.34

If I use =averageif(g131:r131,"e131") I get 40.28

If I use =averageif(g131:r131,"175.00") I get 13.34

The value in E131 changes based on user selection so what I'm trying to do is average only the cells in G131:R131 that do NOT equal the value in E131 (currently 175.00).

Hope that makes sense! Any ideas? I've tried just entering the e131 but it wants the quotes. Really wondering why I get such different answers by referring to a cell instead of a "value".

Many thanks!


I am trying to calculate time in the ER, avg. time in the ER, and time before seen in minutes USING the Averageif function. However, in the columns that I am trying to calculate, there are a lot of ######### and 0:00. So I thought that if I use =AVERAGEIF(AL2:AL47,">0") then that would exclude the ######## and the 0:00.

However, it seems that the formulas are not working. For example, one column contains 00:10, 00:04, 00:05, then a lot of ########, and 0:00.

When I take the =AVERAGEIF(AL2:AL47,">0") of this whole colum then it gives me and average of 18:02!!!!

18 hours and 2 minutes can not be correct!

Please help! Thanks a lot for your time!!

Hello how would I translate an AVERAGEIF to sumproduct?

This formula works:

This doesn't:


I hope someone gets me.

Hello guys, I'm working with Excel 2003 and I desperately need the AVERAGEIF function. Can you give a user defined function for AVERAGEIF... Thanks in advance

How would you calculate the average of the 5 lowest values in ranges B7:B131?

needed formula in b2 for averageif---trying to average d1:d100 if c1:c100 are >= number only in a1. thx in advance

Looking to do an averageif (a2:a100, ">0") but need to average the last 5 numbers that are positive and not the entire column...

Is there such a function as "AverageIf"? I'm looking to average a series of entries only if thay have occured within a particular month. The data table has several rows within a given month, but only a few of the columns have data in them. I know that I can get the numerator with "SumIf", but I am stymied as to how to get the proper count for the denominator for the average computation.

Any suggestions?

Hi , I use countIF, averageIF and sumIF a lot, and I want to do the same with Min and Max (also Mean and Median if possible), how would I do this?

Day Rating Month Year
1 5 Jan 2007
2 3 Jan 2007
3 4 Jan 2007
4 2 Jan 2007
1 1 Feb 2007
2 5 Feb 2007
3 5 Feb 2007

Minimum Rating for January is 2
Minimum Rating for 2007 is 1

The formula will be placed in line with the data and be sortable as with averageIF. (=averageIF(C:C,C1,B:B) would give me the average Rating for January, sorting this gives me months in order of the average rating)

(please note this is a contrived example to show what I mean, I don't really rate days )

I am using excel 2007 and trying to use averageif and I keep getting a DIV/0 error. In Column AD6:AD57 I have percentages. I want to average them only if they are below 100%. Here is my formula

Hello guys! I'm having a problem here. I'm trying to use the AVERAGEIF function in Excel 2003 and it seems that it's not available. Is there any to combine formulas that will give same results as AVERAGEIF does?

(I just can't imagine why our company is still using Excel 2003! Hello? there's already 2010!! LOL)

Thanks in advance...



I did search about this, but couldn't find an answer; advance apologies if i've missed anything obvious.

Have a data set with multiple variables.

have a chart using the last column of variables (AR) and the results column (AS).

am using averageif to get the average of results for each variables.


=AVERAGEIF(AR2:AR1000, "1", AS2:AS1000)

This works fine and I am happy with the results.

however, I would like to use filters on other variables (column A through to AQ), and for the average not to count the filtered data.

So far I can only see possibly using subtotal (not sure how), or writing a macro (no clue how).

Any advice hugely appreciated.

(Using Excel 2007)

Averageif is available on 2007, but not on 2003

trying to calculate average commision on several different lenders.


Column a: Commissions
column b: Lenders

on 2007 i am using =averageif(a1:a20,"lenders",b1:b20) that works fine, but need help with the formula on 2003

=average(if( ????????