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 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 ...
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 ...
Quickly Copy the Last Action to Multiple Cells in Excel
In the previous tutorial I talked about the Redo button in Excel and how using Ctrl + Y can be used to do the same ...
How to Use Multiple Functions and Formulas in a Single Cell in Excel
Lets learn how to put multiple functions and formulas in a single cell in Excel in order to build more complex form ...

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 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 have 2 formulae that work just fine

How do I get one to show the average of the values of the instances in between, ie <150000 AND >=50000?

=AVERAGEIF($D$5:$D$340,"<150000",$D$5:$D$340)*AND(AVERAGEIF($D$5:$D$340,">=50000",$D$5:$D$340)) returns an incorrect value
as does =SUMIF(D$5:D$340,"<150000")-(SUMIF(D$5:D$340,">=50000")))/(COUNTIF(D$5:D$340,"<150000")-COUNTIF(D$5:D$340,">=50000"))

What am I missing?

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

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

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

Hi All,

This is a great forum and I can usually find my answer just through searching; however, I am stuck and need to ask for some assistance. I have the following formula I am using to create a running averageif for the name in column J based on the data in df (=AVERAGEIF($J$2:J2,J:J,$DF$2:DF2). This sheet has 1500 rows, and their is a historical sheet in the same format that has over 6000 rows. I am trying to alter this formula, in another column, to get the running averageif for the last 3,5, or 10 occurrences only. Any help is greatly appreciated.



PS - I have excel 2007. I see it says 2003 under my name

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)


I used AverageIf for my data, I am looking for a function that works like AVERAGEIF but instead of average, calculating Variance.
Here is how I use it :
If the shift column is "1st" then calculate the average of time.
I've attached the sample of my excel file.

Thank you in advance for helping me.

Hi All,

Currently having major excel issues. I've got a spreadsheet with multiple columns of data all feeding from different sources, and I need to do an average based on multiple criteria.

So I currently have to sort one column alphabetically so that I can do an average if with the range being the range of few values from the column which i have.

So they currently look like this:
=AVERAGEIF($T$72:$T$222,"Criteria B",$AG$72:$AG$222)
=AVERAGEIF($T$2:$T$71,"Criteria A",$AG$2:$AG$71)

where $T$72:$T$222 is Criteria C, and $T$2:$T$71 is Criteria D
Column AG is where the data I want to average is.

Ultimately I want to make it so that excel will do an average of all the data which has all the criteria required.

Thanks for any help!

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

I'm trying to average cells across multiple sheets, excluding 0's, but the formula I want to use isn't recognized by Excel.

=AVERAGEIF(Sheet1!l12,Sheet2!l12,Sheet3!l13, "<>0")

Any help?