Close Window   
Free Ebook
Got an Excel Question?
Ask it in our Excel forum!
Subscribe for Free Excel tips & more!

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 Tutorials

Select Ranges of Cells in Excel using Macros and VBA
This Excel VBA tutorial focuses specifically on selecting ranges of cells in Excel.  This means that you will learn ...
Allow Only Certain People to Edit Specific Cells in Excel
How to allow only certain people to edit certain cells or ranges in Excel. This is a security feature that you can ...
Average Non-Contiguous Cells in Excel
How to average non-contiguous cells (cells that aren't next to each other) if those cells contain numbers and are ...
Naming Cells in Excel to Make Using Formulas/Functions Easier
In this tutorial I am going to introduce the idea of Named Cells. A Named Cell is a cell which you have given a cus ...

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.

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.


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 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 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?

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

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

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)

Hello there,


This averages all the values in the range that are greater than or equal to 6.8...

However, I wish that instead of static "6.8" it could link to another cell, A9;

i.e. to average all the values in the range that are greater than or equal to the number in A9.

=AVERAGEIF(C11:C251,">=A9") returns DIV#0.



Hello all!

I have the following table of information:

Date Month Value
8/9/2008 8 1
8/9/2008 8 2
8/9/2008 8 6
9/2/2008 9 12

...and the following formula works fine returning a value of 3:


However, when I attempt to use the MONTH function within the Averageif function:


...I keep receiving the error "The formula you typed contains an error."

Any ideas on what I am doing wrong?


W ant to know how to =Averageif with 2 different criteria












Sum Average






Average Between Hi / Lo


i.e. want to average all numbers in Value Column tat are between Hi and Lo value

Any help would be appreciated.

Kind Regards


I am using the following formula, but for the cells that dont have data I get the #DIV/0! error.


Help please!

I'm trying to get a simple average of cells (some containing no values but skewing my result). The cells I'm selecting are selected by holding the ctrl key and choosing several per that method. The Averageif won't work b/c of the way (i.e. holding ctrl key, selecting a cell (or cells) and entering a comma after each selection) I'm choosing what I want to be my "array."

Averageif might not be the best formula to use but at the end of the day, I'd like to select certain cells by the above-mentioned method and not have those cells w/ no value (but contain a formula in their own right) skew my simple average calculation.

Note: I can't do a sum and then divide by countif due to the same "hiccup."

Thanks :0)

I'm sure this is a very easy problem to fix but why isnt my averageif formula working?!




I would like to average the values that accour during the week excluding Mondays and Fridays. For that I created the following formula but I beleive it is wrong.


A B 1 MON 236 2 TUE 467 3 WED 765 4 THU 345 5 FRI 897
Additional Details

Please notice that this formula works very well if only one day of the week is excluded from the criteria. The problem that I am having is when I add more than one day to the formula, it ignores the second day.

=AVERAGEIF(A1:A5,"MON",B1:B5) This formula works fine.

Please keep in mind that what I need is to exclude ( not equal to ) the values from Mondays and Tuesdays. In this exmple the correct result should be 525 or 526 depending on the rounding. Thanks.

so i'm trying to get mroe accurate data out of my reports. I'm looking for the Average of a column excluding anything higher then a certain mark and equaling the column next to it. Not sure if totally possible. Still new to excel


10 10
15 17
20 21
35 35
40 41
47 47

So say for htis. I want the average of Column A over the A and the Average of B over B. that i have. But over those numbers. I want the Average of Column A excluding anything 35 and over that equals column B. So Average everything but the 35 and 47. ..And the same for Column B. Thanks for any assistance you can provide


edit: i'm not stuck on the AverageIf formula if it needs to be changed, i'm all ears..or eyes....

I'm trying to convert a large spreadsheet to 97-03 compatibility, and I'm getting errors on all my AVERAGEIF() formulas. What is the previous approach to averaging while ignoring "0" cells?

Current formula causing issues:

=AVERAGEIF(H5:H200, "<>0")

What is an older formula that accomplishes the same thing?


I have been given an excel 2007 model but I only have Excel 2003 on my work computer. When I have opened up the model, I got #name in the cells where there is this formula. After doing somer research I learned that AVERAGE(IF( should be used; some say as an array formula, other's don't. I was wondering how to do it for this AVERAGEIF formula:


Thanks so much for the help

Hi everyone

I am fairly new to VBA and are busy compiling my first userform. i am however now stuck on a formule that i want to implement.

Background on userform:
I have 240 textboxes that needs to be completed, sorted in 10 columns (1 entry per hour for 10 different readings e.g.volts / Watts etc)

I then have a button on worksheet that is suppose to calculate the average per day for all 10 colums and transfer it to another userform.

I am however struggling with the form as the normal average formula counts any zero's causing me to have the wrong average. I therefore need to use the AverageIf.

My current average formula is very long, as i do not know how to use ranges etc, but i am posting it to let you see what i am currently doing:



Electrode1_Slip = Application.WorksheetFunction.Average(E1_SLIP_01, E1_SLIP_02, _
    E1_SLIP_03, E1_SLIP_04, E1_SLIP_05, E1_SLIP_06, E1_SLIP_07, E1_SLIP_08, _
    E1_SLIP_09, E1_SLIP_10, E1_SLIP_11, E1_SLIP_12, E1_SLIP_13, E1_SLIP_14, _
    E1_SLIP_15, E1_SLIP_16, E1_SLIP_17, E1_SLIP_18, E1_SLIP_19, E1_SLIP_20, _
    E1_SLIP_21, E1_SLIP_22, E1_SLIP_23, E1_SLIP_24)

How do i need to change this now to only average if amount is more than zero? (PS default amount for textboxes are set as 0 already)

Any help will be appreicated