Sumif/averageif With Multiple Ranges 
New Excel Help Service!
We need your input to help create a new Live Online Excel Help service.
Please take our survey and let us know your thoughts!
Have a great day!
Don


Sumif/averageif With Multiple Ranges  Excel 
View Answers 
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!!!
=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 Tutorials
Wildcards in Excel
Wildcards are characters that allow you to make more robust functions, searches, and filters in Excel. You put wil ...
Wildcards are characters that allow you to make more robust functions, searches, and filters in Excel. You put wil ...
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 ...
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 ...
How to allow only certain people to edit certain cells or ranges in Excel. This is a security feature that you can ...
Average NonContiguous Cells in Excel
How to average noncontiguous cells (cells that aren't next to each other) if those cells contain numbers and are ...
How to average noncontiguous 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 ...
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 Resize Multiple Columns or Rows at Once in Excel
How to quickly resize multiple columns and rows at once in Excel. This avoids having to individually resize rows ...
How to quickly resize multiple columns and rows at once in Excel. This avoids having to individually resize rows ...
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
 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
 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
 This macro will allow you to delete multiple named ranges very quickly. When you run this macro, a message box will pop
PopUp 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
 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
 This macro will change all text within the selected cells to uppercase. It works only on selected cells within Microsoft
Similar Topics
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
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
=AVERAGEIF(AD6:AD57,AD6:AD57
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)
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)
Hello,
I'm sure this is a very easy problem to fix but why isnt my averageif formula working?!
=AVERAGEIF(Sheet2!A:A,"<=A3&>A4",Sheet2!D:D)
Thanks,
Rich
I'm sure this is a very easy problem to fix but why isnt my averageif formula working?!
=AVERAGEIF(Sheet2!A:A,"<=A3&>A4",Sheet2!D:D)
Thanks,
Rich
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.
=AVERAGEIF(A1:A5,"MON","FRI",B1:B5)
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.
=AVERAGEIF(A1:A5,"MON","FRI",B1:B5)
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.
Hi,
I accidentally opened a 2007 Excel Workbook (containing an AVERAGEIF formula) in Excel 2003 and now I automatically have the following named range:
Name: _xlfn.AVERAGEIF
Refers to: =#NAME?
I believe it is the reason why I keep getting a popup box that says, "A formula in this worksheet contains one or more invalid references. Verify that your...etc.", whenever I try to save my file.
How do I fix this/remove it? It won't let me delete the named range.
Thanks for all your help...much appreciated!!!
John
I accidentally opened a 2007 Excel Workbook (containing an AVERAGEIF formula) in Excel 2003 and now I automatically have the following named range:
Name: _xlfn.AVERAGEIF
Refers to: =#NAME?
I believe it is the reason why I keep getting a popup box that says, "A formula in this worksheet contains one or more invalid references. Verify that your...etc.", whenever I try to save my file.
How do I fix this/remove it? It won't let me delete the named range.
Thanks for all your help...much appreciated!!!
John
Dear Forum:
Excel 2007
Please see attachment since my question will be relative to it.
I would like to calculate GEOMEAN in the C column similar to how I have the AVERAGEIF calculated. The data in Column A will have duplicate entries, but will always be grouped (range) together. I want to calculate GEOMEAN for the values in Column B. Ideally the GEOMEAN for B1:B4 will be calculated in C1, the GEOMEAN for B5:B7 will be calculated in C:5, etc., but if it is easier to have the GEOMEAN calculated like I have with AVERAGEIF (every cell in C Column populated with data) then that is fine too.
Thanks in advance.
Scott (greenhorn/newbie)
Excel 2007
Please see attachment since my question will be relative to it.
I would like to calculate GEOMEAN in the C column similar to how I have the AVERAGEIF calculated. The data in Column A will have duplicate entries, but will always be grouped (range) together. I want to calculate GEOMEAN for the values in Column B. Ideally the GEOMEAN for B1:B4 will be calculated in C1, the GEOMEAN for B5:B7 will be calculated in C:5, etc., but if it is easier to have the GEOMEAN calculated like I have with AVERAGEIF (every cell in C Column populated with data) then that is fine too.
Thanks in advance.
Scott (greenhorn/newbie)
I am trying to do a conditional function across multiple tabs and multiple cells. Is it possible?
=AVERAGE('Sheet 1:Sheet10'!a15:a2000) works fine but when I add in the conditional if I receive a value error.
=AVERAGEIF('Sheet 1:Sheet 10'!C115:C2000,B2,'Sheet 1:Sheet10'!A15:A2000)
=AVERAGEIFS('Sheet 1:Sheet 10'!A15:A2000,'Sheet 1:Sheet 10'!C15:C2000,B2)
=AVERAGE('Sheet 1:Sheet10'!a15:a2000) works fine but when I add in the conditional if I receive a value error.
=AVERAGEIF('Sheet 1:Sheet 10'!C115:C2000,B2,'Sheet 1:Sheet10'!A15:A2000)
=AVERAGEIFS('Sheet 1:Sheet 10'!A15:A2000,'Sheet 1:Sheet 10'!C15:C2000,B2)
Hi all,
So AVERAGEIF has been very nicely explained to me at, and its a incredibly useful functions.
The averages I've taken from my dataset I then want to know their standard deviation. There doesn't seem to be, though, an equivalent function for standard deviation (STDEVIF if you will).
Can anyone suggest a function or a formulaic workaround that would allow me to get standard deviations of averages taken from AVERAGEIF.
Thanks!
So AVERAGEIF has been very nicely explained to me at, and its a incredibly useful functions.
The averages I've taken from my dataset I then want to know their standard deviation. There doesn't seem to be, though, an equivalent function for standard deviation (STDEVIF if you will).
Can anyone suggest a function or a formulaic workaround that would allow me to get standard deviations of averages taken from AVERAGEIF.
Thanks!
I am trying to sum all numbers <0 using the SUMIF formula in Excel 2003.
FORMULA: =sumif(range,criteria,sum_range)
I got it to work, but I have cell ranges to total that are not next to each
other (such as A5:A15 and C5:C15).
When selecting the ranges for the formula, Excel writes them A5:A15,C5:C15
because of the comma (,) between the cell ranges Excel is reading the A5:A15
as the range and C5:C15 as the critera. How can I get this formula to allow
me to select multiple cell ranges?
All help is appreciated.
Thank you.
FORMULA: =sumif(range,criteria,sum_range)
I got it to work, but I have cell ranges to total that are not next to each
other (such as A5:A15 and C5:C15).
When selecting the ranges for the formula, Excel writes them A5:A15,C5:C15
because of the comma (,) between the cell ranges Excel is reading the A5:A15
as the range and C5:C15 as the critera. How can I get this formula to allow
me to select multiple cell ranges?
All help is appreciated.
Thank you.
Does anyone know a formula to average multiple ranges but ignore zeros. I can do this over one range but adding other ranges confuses everything?Thanks
Hey,
Id like to find an two month average for data, but I get a #DIV/0! error. Its fine when I try the averageif one month average, but not for more than one. Formula below:
=AVERAGEIFS(O:O,D:D, "Jul",D:D, "Aug")
Any resolutions for this?
Cheers.
Id like to find an two month average for data, but I get a #DIV/0! error. Its fine when I try the averageif one month average, but not for more than one. Formula below:
=AVERAGEIFS(O:O,D:D, "Jul",D:D, "Aug")
Any resolutions for this?
Cheers.
I have a column of numbers that I want to get an average of only if the number is greater than zero AND only if it corresponds to a specific name in another column.
Am a little stuck with the average if function.
I want to average the values in column G (where populated) if Column A=PHA
It's monday afternoon! well,that's my excuse for being rubbish today!
I want to average the values in column G (where populated) if Column A=PHA
It's monday afternoon! well,that's my excuse for being rubbish today!
I'm trying to sum a series of quarterly data into years. The sum range is two or more adjacent rows. The following formula only sums the first row, 184, and not row 185
=SUMIF($D$14:$BS$14,D$368,$D184:$BS185)
I've searched the forum, but must not be posing the questions correctly as I get a lot on info on multiple criteria ranges, but not multiple sum ranges.
=SUMIF($D$14:$BS$14,D$368,$D184:$BS185)
I've searched the forum, but must not be posing the questions correctly as I get a lot on info on multiple criteria ranges, but not multiple sum ranges.
Hey all,
I am trying to setup of a worksheet that has ranges A:F as a named
ranged using the name "DB1_#" (# = Cells 1  65535), I have noticed
that when I try to create a mass quantity of named ranges Excel craps
out on me at around 65407...anyone have any idea as to why this is or
if there is a macro I could run to create a worksheet with 65535 named
ranges using that setup? See example below if your confused...
ROW 1: DB1_1 (Ranges A:F)
ROW 2: DB1_2 (Ranges A:F)
ROW 3: DB1_3 (Ranges A:F)
ROW 4: DB1_4 (Ranges A:F)

ROW 65536: DB1_65536 (Ranges A:F)
Thank you kindly!
I am trying to setup of a worksheet that has ranges A:F as a named
ranged using the name "DB1_#" (# = Cells 1  65535), I have noticed
that when I try to create a mass quantity of named ranges Excel craps
out on me at around 65407...anyone have any idea as to why this is or
if there is a macro I could run to create a worksheet with 65535 named
ranges using that setup? See example below if your confused...
ROW 1: DB1_1 (Ranges A:F)
ROW 2: DB1_2 (Ranges A:F)
ROW 3: DB1_3 (Ranges A:F)
ROW 4: DB1_4 (Ranges A:F)

ROW 65536: DB1_65536 (Ranges A:F)
Thank you kindly!
Hello,
I have a big set of data with various characteristics. I need to calculate average and stdev of a subset belonging to particular group. I used "averageif" function  worked perfectly  and am looking for something like"stdevIF"  can someone help me, please?
Thanks in advance,
V.
I have a big set of data with various characteristics. I need to calculate average and stdev of a subset belonging to particular group. I used "averageif" function  worked perfectly  and am looking for something like"stdevIF"  can someone help me, please?
Thanks in advance,
V.
Hi,
Is there a way to specify discontigous ranges for the Holidays parameter when I am not specifying them as a constant. I tried:
=NETWORKDAYS(G5;H5;Geirkalender) where GeirKalender is defined as:
=Geir;Vacation that is to separate ranges.
But all I get is "VAlue error"
Is there a way to specify discontigous ranges for the Holidays parameter when I am not specifying them as a constant. I tried:
=NETWORKDAYS(G5;H5;Geirkalender) where GeirKalender is defined as:
=Geir;Vacation that is to separate ranges.
But all I get is "VAlue error"
I want to use multiple ranges (named) as the data source for a pivot table
using MS Query. When I import the workbook my options are only to select the
"tables" (which are my sheets referenced as sheetname$). I don't want to use
the entire sheet, just my named ranges in multiple sheets.
Please help.
Kathy H
using MS Query. When I import the workbook my options are only to select the
"tables" (which are my sheets referenced as sheetname$). I don't want to use
the entire sheet, just my named ranges in multiple sheets.
Please help.
Kathy H
I've tried on it a hard but still unable to get solution ....
Actually with the help of array formula I"m taking out Average, Min, Max value of ranges and I was omitting zero but the problem is occurring that if i have originally zero in my ranges then Average & Minimum function is not working properly. In attached sheet i've defined problems in a very specific manner ........
Actually with the help of array formula I"m taking out Average, Min, Max value of ranges and I was omitting zero but the problem is occurring that if i have originally zero in my ranges then Average & Minimum function is not working properly. In attached sheet i've defined problems in a very specific manner ........
I have two Ranges, which I view as Arrays or Matrices, I want to subtract the two (which do not overlap or union in any way) to get a new third range of the differene. This new range should be the same size, and can be considered a difference of each cell in the range. Note both ranges are already identical in size.
Any help solving this, searched up and down for hours.
Note; Im trying to avoid looping, as this might become incredibly inefficient if the ranges grow to big, looking for alternative that is faster, someone said maybe excel.evaluate
Any help solving this, searched up and down for hours.
Note; Im trying to avoid looping, as this might become incredibly inefficient if the ranges grow to big, looking for alternative that is faster, someone said maybe excel.evaluate
Good morning  a newbie here!
I have a monthly excel driven accounts spreadsheet with named ranges to identify noncontiguous areas to print an accounts pack for distribution to nonexcel customers. The ranges are all contained in the one worksheet tab of a multi worksheet file.
Currently, I manually print the differing ranges to individual pdf's, then manually consolidate the individual pdf's to one master pdf.
I tried recording a print macro to a normal printer, and this works fine  I get a consolidated hard copy accounts pack. But I then tried to modify the recorded VBA to get it to direct to pdf output with no luck.
Does anyone have any ideas? I've spent months trawling the web on this but nothing seems to fit the specific requirement of multiple named ranges consolidated to one pdf.
Thank you
I have a monthly excel driven accounts spreadsheet with named ranges to identify noncontiguous areas to print an accounts pack for distribution to nonexcel customers. The ranges are all contained in the one worksheet tab of a multi worksheet file.
Currently, I manually print the differing ranges to individual pdf's, then manually consolidate the individual pdf's to one master pdf.
I tried recording a print macro to a normal printer, and this works fine  I get a consolidated hard copy accounts pack. But I then tried to modify the recorded VBA to get it to direct to pdf output with no luck.
Does anyone have any ideas? I've spent months trawling the web on this but nothing seems to fit the specific requirement of multiple named ranges consolidated to one pdf.
Thank you
I am attempting to use the AVERAGEIFS function in Excel 2007. The other two criteria and criteria range I am using in the function work very well. Now I am trying to add a third criteria where any averaged cell with a #N/A error is not attempted to be averaged. Right now, it is trying to average a range of cells that may contain an #N/A error. I would like to figure out how I can use the AverageIfs function and a specific criteria to have it not average any cells that contain the error but will still average all the other cells in the range that actually contain a number. Right now, the AverageIfs function just returns an #N/A error if any of the averaged cells contain an #N/A.
The reason my averaged range contains errors (#N/A) is because I am charting that range and do not want the error data to be shown on the chart.
The reason my averaged range contains errors (#N/A) is because I am charting that range and do not want the error data to be shown on the chart.
Greetings all,
I have a file with a number of named ranges. I would like to have a routine that lists out all of the named ranges (throughout the file, not just on one page) and display all of these names on a sheet.
I have come up with this loop, but it doesn't work exactly right just yet.
Basically, it gets to the last name in the workbook and then fills in 100 cells with the range. I arbitrarily picked 100; I really would like to loop through all named ranges in the file, whether there are 10, 100, or 1000 ranges.
I would like each row to be a unique name. Is there a way to list the name AND the range that the name refers to?
Thanks in advance.
Kind Regards,
Sean
Public Sub ShowNames()
Dim Nm As Name
Dim i As Long
For Each Nm In ActiveWorkbook.Names
For i = 1 To 100
Range("A1").Offset(i, 0).Value = Nm
Next i
Next Nm
End Sub
I have a file with a number of named ranges. I would like to have a routine that lists out all of the named ranges (throughout the file, not just on one page) and display all of these names on a sheet.
I have come up with this loop, but it doesn't work exactly right just yet.
Basically, it gets to the last name in the workbook and then fills in 100 cells with the range. I arbitrarily picked 100; I really would like to loop through all named ranges in the file, whether there are 10, 100, or 1000 ranges.
I would like each row to be a unique name. Is there a way to list the name AND the range that the name refers to?
Thanks in advance.
Kind Regards,
Sean
Public Sub ShowNames()
Dim Nm As Name
Dim i As Long
For Each Nm In ActiveWorkbook.Names
For i = 1 To 100
Range("A1").Offset(i, 0).Value = Nm
Next i
Next Nm
End Sub
Hello all,
Does anyone know how to find hidden named ranges in Excel?
My company has added software to Excel where the software can determine errors in your spreadsheet. For example, this software can find cells that reference blank cells and cells that have the same color font and background (as in, you are trying to hide the results of the formula). In addition, this software lists out all named ranges in a spreadsheet.
When I ran the software, it was determined that I have many named ranges within this spreadsheet. My first step was to delete all named ranges by choosing InsertNameDefine and deleting all named ranges (including set print areas). Basically, I was trying to get rid of all named ranges.
After I did this, there are still many named ranges within Excel, even though they do not show up in the InsertNameDefine menu. I tried to do a find (with ControlF) for some of these ranges, but was unsuccessful. Does anyone know how to delete these hidden named ranges?
One related issue  Every time I try to copy a sheet in this file, I get numerous messages indicating "A formula or sheet you want to move or copy contains the name "XXXXXXX", which already exists on the destination worksheet. Do you want to use this version of the name?" I get this message with every named range that exists (11 times). The "XXXXXXX" can be replaced with the other named ranges.
One final note  I am using Excel 2002.
Thanks!
Does anyone know how to find hidden named ranges in Excel?
My company has added software to Excel where the software can determine errors in your spreadsheet. For example, this software can find cells that reference blank cells and cells that have the same color font and background (as in, you are trying to hide the results of the formula). In addition, this software lists out all named ranges in a spreadsheet.
When I ran the software, it was determined that I have many named ranges within this spreadsheet. My first step was to delete all named ranges by choosing InsertNameDefine and deleting all named ranges (including set print areas). Basically, I was trying to get rid of all named ranges.
After I did this, there are still many named ranges within Excel, even though they do not show up in the InsertNameDefine menu. I tried to do a find (with ControlF) for some of these ranges, but was unsuccessful. Does anyone know how to delete these hidden named ranges?
One related issue  Every time I try to copy a sheet in this file, I get numerous messages indicating "A formula or sheet you want to move or copy contains the name "XXXXXXX", which already exists on the destination worksheet. Do you want to use this version of the name?" I get this message with every named range that exists (11 times). The "XXXXXXX" can be replaced with the other named ranges.
One final note  I am using Excel 2002.
Thanks!