
Sumif/averageif With Multiple Ranges


Search Excel Forum Posts, Tutorials, Macros, Tips, and More
This is what i was using,
=AVERAGEIF($B$198:$B$240,B254,$C$198:$C$240)
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!!!
Similar Excel Video Tutorials
Pivot Table AVERAGEIF SUMIF/COUNTIF
 See how to average with criteria using a Pivot Table (PivotTable), the AVERAGEIF function and the SUMIF/COUNTIF functions! Also see how to create many ...
SUMIF Only Accept Ranges, Not Arrays
 Learn about the limitations of the SUMIF function it cannot accept arrays, only ranges. This means you must use other means like the SUMPRODUCT funct ...
Helpful Excel Macros
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
Hello,
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 15 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 610 years & 1115."
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.
=??AVERAGEIF??(A1:A30,"Time",S1:S30)
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.
=averageif(G236:G238,I236:I238,J236:J238,">0"),G236:G238,I236:I238,J236:J238)
I know this won't work.
Options?
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:
=AVERAGEIF($a$1:$a$5,"
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.
Sheet1
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
Hi
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:
=AVERAGEIF(H3:H12,">0",H3:H12)
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
=IF(M3:M12=0,"",(AVERAGEIF(M3:M12,">0",M3:M12)))
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...
Cheer
Drac
Hi Excel Gods
I currently have this equation:
=AVERAGEIF($B$2:$B$900000,">="&L5,$C$2:$C$900000)
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?
Thanks!
Hello,
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.
Thanks
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")
=AVERAGEIF(K3:K6,"<>0")
=AVERAGEIF(K3:K7,"<>0")
etc in the consectutive cells running down the page.
But The result I get is =AVERAGEIF(K4:K5,"<>0")
=AVERAGEIF(K5:K6,"<>0")
=AVERAGEIF(K6:K7,"<>0")
ETC.
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!
Gino
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:
=AVERAGEIF(C2:C5;"y";B2:B5)
This doesn't:
=SUMPRODUCT((C2:C5="y");AVERAGE(B2:B5))
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 averageiftrying 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?
Example:
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
=AVERAGEIF(AD6:AD57,AD6:AD57
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...
Regards^^
Hi,
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.
e.g.
=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.
Example:
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( ????????

