Free Microsoft Excel 2013 Quick Reference

formula using multiple ranges

i would like to use an IF formula to query two ranges of cells (on separate
worksheets in the same workbook) but don't know how to enter more than one
range in the formula. please can you help


Post your answer or comment

comments powered by Disqus
I am using DSUM formula using multiple criteria where one of the criteria is a tag (i.e. 1=salaries; 2=taxes;3=suppliers etc) and the other criteria is date within a range of dates (i.e. between TODAY () AND TODAY()+30. I can't set this up to include the date criteria.

Please help.

First time posting, so be gentle.

I have found myself in the midst of massive excel project with so many constraints placed on me that I have reached my wits end.

I can not use VBA in the project. It must be accomplished strictly using formula and standard functions from Excel 2007.

I can not change the layout. I must use the poorly designed sheet as is.

It is possible I may be able to blow the whole thing up and do it right at some point, but I can't do that now.

I have resolved most of the issues through endless searching on this forum and others and through countless hours of beating my head into a brick wall.

Here is my dilemma.

On one data sheet there is a column that contains text that I would best describe as a formula concept. You can think of this as text of a formula using named ranges. So something like =(Data1/8)+(Data2/16)+(Data3). Realize that there may be as may as 15,000 records. Most of them will be blank indicating that special conditions do not apply. When there is text information there it indicates that special conditions do apply and what calculations are needed to be applied. Each formula may be unique and not applied to another record. Also records will be added on a regular basis.

Further complication is that the named ranges are limited to the scope of each of 1 of 6 calculation sheets. Each of the 6 sheets has a cell defined as data1, data2, data3 etc. that is limited to the scope of the sheet. Each of the 6 sheets may have different value of data 1, etc.

I actually managed to use the EVALUATE function in a named range to get the formula pulled to the 6 sheets and the formula resolved to the correct amounts. However when the value of the named ranged data1 changes, it does not update the value of the named range including the EVALUATE function.

I tried recalculating the sheet but that did not work. I could not get it to update the value other than by closing and reopening the sheet.

I have attached a simplified example of what I am working with. Please realize the sheet2 is actually repeated 6 times and each different sheet 2 has a ranged named data1 restricted to that sheet and that may be of different values.

I can change the naming conventions in whole or part, but I can not use VBA or change the page layout.

Thanks in advance for any ideas on this conundrum.

SAMPLE.xlsx

What is the correct formula if I want to find the average for numbers that
are in multiple ranges and I don't want to count empty cells in those
multiple ranges? For example: my numbers are in ranges A4:A11 and A16:A23 and
A30:A44. Keep in mind that some of the cells in those ranges are empty so I
don't want to count them. Thanx in advance for your help...

I am looking for a function to search through multiple ranges of cells
and return a number if it finds any instances of that number.

For instance:
I have say 6 cells, (A1, C17, F13:G14) and I want to find out if any of
them contain the number "1" and if they do, I want the result of B2 to
be "1".

The way I thought I could accomplish this was to go to cell B2 and do
the formula =if(countif((A1,C17,F13:G14),"1")>0,1,0)

This seems to work for a single range such as
=if(countif(F13:G14,"1")>0,1,0)

I just dont know the proper syntax for including multiple ranges in a
function.

If anyone knows how to include multiple ranges in a function (if its
possible), or knows a simpler way to do what I am trying to do, the
help would be greatly appreciated.

--
solinar
------------------------------------------------------------------------
solinar's Profile: http://www.excelforum.com/member.php...o&userid=31159
View this thread: http://www.excelforum.com/showthread...hreadid=508216

I am looking for a function to search through multiple ranges of cells and return a number if it finds any instances of that number.

For instance:
I have say 6 cells, (A1, C17, F13:G14) and I want to find out if any of them contain the number "1" and if they do, I want the result of B2 to be "1".

The way I thought I could accomplish this was to go to cell B2 and do the formula =if(countif((A1,C17,F13:G14),"1")>0,1,0)

This seems to work for a single range such as =if(countif(F13:G14,"1")>0,1,0)

I just dont know the proper syntax for including multiple ranges in a function.

If anyone knows how to include multiple ranges in a function (if its possible), or knows a simpler way to do what I am trying to do, the help would be greatly appreciated.

I need an "IF" formula that deals with multiple ranges (conditions). I'm trying to develop an order template for pre-season booking orders, which earn different discounts at different purchase levels. Let me write it out.

If value of CellX is greater than 5000 and less than 10000, then CellZ value shall be 2,
but if value of CellX is greater than 10000 and less than 15000, then CellZ value shall be 3
but if value of CellX is greater than 15000 and less than 20000, then CellZ value shall be 4
but if value of CellX is greater than 20000, then CellZ value shall be 5

I hope I've made it clear. If you have any questions, PLEASE let me know. I've never worked in "IF" formulas before, so this is pretty new to me.

Thank you.

Hi

I have a macro set up with countif and sumif formulas to calculate my virtual horses race count, nomination fee, stakes returns etc. It works well but it could be improved.

I previously searched the whole cell so all the horses in my table would have their data calculated from the whole season. The problem I have is I may buy a horse mid season and would like to calculate the data based on the date I bought the horse.

I have managed to adjust my old macro so that it searches ranges as opposed to cells, but I cannot work out how to write in the code how to search for multiple ranges.

To explain further it may be I buy several horses on the same day, so the ranges from when I want to start calculating their data from will be the same. At the minute I can only work out how to change the macro to search one range. Is there a way to adjust the macro so that in my example attached the three sample horses in my table are all calculated from the same set of formulas?

I have pasted the code below to show how the macro is set up:

Sub
MyHorseSearch()
Application.ScreenUpdating = False
Dim cell As Range
For x = 3 To 5

If Cells(x, 1).Interior.ColorIndex <> 15 Then
End
Else

With Application.WorksheetFunction
'formulas for G1 Graded racing'
Range("b3").Value = .CountIf(Sheets("G1 Graded").Range("d7:d700"),
Range("a3").Value)
Range("c3").Value = .SumIf(Sheets("G1 Graded").Range("d7:d700"), Range("a3").Value,
Sheets("G1 Graded").Range("v7:v700"))
Range("d3").Value = .SumIf(Sheets("G1 Graded").Range("d7:d700"), Range("a3").Value,
Sheets("G1 Graded").Range("t7:t700"))
Range("e3").Value = .SumIf(Sheets("G1 Graded").Range("d7:d700"), Range("a3").Value,
Sheets("G1 Graded").Range("u7:u700"))
Range("f3").Value = Range("e3") - Range("d3")



End With
End If
Next
Application.ScreenUpdating = True
End Sub
I thought that if I changed it to for example
it would calculate them but it came up 'run time error 13: type mismatch.'

I am wanting this set up because I may be tracking hundreds of horses, and if I enter formulas directly into excel cells it will really slow the sheet down.

If anyone has any guidance on where I am going wrong I would be really grateful of the help.

Thanks
Gemma

Hi, I'm using multiple ranges for the first time in excel, AND i'm using 2007 to learn it in.

I have to keep the file in compatibility mode though, as 2003 users will need to use it.

When i use the pivot table wizard and add the two ranges i'm using and finish the wizard, it comes up with the following fields to choose from: Row, Column, Value & Page1. I want it to say: Account, Date, Details, Amount, Job, Ref etc.

The data is one account with every transaction over the whole year, it's about 90,000 lines. I want the pivot table to recognise that it is one set of data and give me the information i need, but it only shows me these 'Rows' and 'Column' choice. I want to be able to choose my data set, column and row from the headings at the top of each range (which are exactly the same)

Please help me!!

Thanks
Charissa

I have the below code "begining"

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A2:A100")) Is Nothing Then Exit Sub


...........
What I want to do is use multiple ranges like so:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A2:A100, D2:D100")) Is Nothing Then Exit Sub


...........

However, I want different vba dependant on which of the above triggers.

So.

If Range("A2:A100") triggers,

Do this

If Range("D2:D100") triggers,

Do That

Something like the above.

Or do I run two completely seperate
"Private Sub Worksheet_Change(ByVal Target As Range)" .........

In one sheet?

I've been struggling to figure out why my Conditional Sum formulas using multiple criteria keep reporting zero. I am tracking resource loads for a project which is divided into 4 parts (Iterations). I am trying to sum the total resource allocations per Iteration. Any help or suggestions would be greatly appreciated....see attachment!

I have a worksheet that I am trying to solve the sum of 1 column of dollar amounts, but I have 2 ranges that can affect whether or not it is included in the sum.

ECP Standard $1,199.00
ECS Committed $449.00
ECS Committed $449.00
MCS Committed $298.00
MCS Committed $298.00
MCS Committed $298.00
ECS GOA $150.00
ECS Committed $430.00
ECS Committed $430.00
MCS Standard $349.00
ECP Committed $1,000.00
ECS Standard $599.00
ECP Standard $1,000.00
ECP Standard $1,000.00
ECP Standard $1,000.00
ECS Standard $599.00
TCS Committed $175.00

So I want to be able to come up with 3 different sums based on A = either EC/MC/TC and then if B = Committed. Not sure how to solve for SUM when using multiple ranges, and conditions...

IF(RANGE $A2:$A34="MC*" AND RANGE $B2:$B34="Comm*",SUM($C2:$C34)

Hope that makes sense....

Any suggestions would be greatly appreciated.

Rich -

Smile like you mean it!

Hey guys,

I'm trying to create a macro so that whenever i hit the keys assigned to the macro, it will bring up a serious of pop-up input boxes, so that i can enter data (both numeric and text) and then have it create a pre-defined formula using the information that was entered into the boxes.

For instance, one formula i would like to make a macro for would be =cw_act("X","Y->Z"), where X, Y, and Z would be populated by the input boxes. Any help would be greatly appreciated. Thanks!

I am trying to paste a formula in multiple cells and I want the range to stay
the same throughout the sheet. When pasting it obviously changes the range
to the next cell in sequence. I'm using the following formula
=SUMPRODUCT(--(A2:A4000=70),--(B2:B4000="Product Type"),C2:C4000) and want to
kep the range A2:A4000, same for column B and C.

Hi there,

I have a total of 3 worksheets. The first worksheet shows record details for site A while the 2nd worksheet shows record details for site A as well, but this is more of a supplementary worksheet. The 3rd worksheet is for reporting.

What I would like:

Use a countifs formula to count values found in multiple ranges based on conditions. Please see the formula I've used in cell C2 in "reporting sheet"

=countifs('Site A Main'!A:A+'Site A sub'!A:A, $A2,'Site A Main'!B:B+'Site A sub'!B:B,$b2,'Site A Main'!C:C+'Site A
sub'!C:C,$C2)

When I press enter, excel is telling me that the formula contains an error.

The reason I'm + ranges as ONE is because two worksheets are involved for site a.

Do you understand where I am going with this? Maybe countifs is not suitable here when it comes to group two ranges as ONE RANGE to apply the conditions on?

Thank you for any possible guidance.

Thanks for the help in the previous post guys, this forum has been a big help so far. Im just getting into excel here and was wondering if this was even possible.

With my previous formula there are 3 variables, and within all my calculations I will ever perfrom with this formula the 3 variables will always have a range of values between "X=1-50", "Y=1-49", "Z=1-48".

so thats 50*49*48= alot of calculations to solve (117600), Is there anyway to have excel output multiple solutions to my formula, with all the variables being between those ranges? (Im having a hard time wording this correctly, I hope it comes off right)

Im not looking for a hundred thousand rows of information in reality, but if theres a way to cutdown on the amount of actual typing/pasting whatever, I would love to know.

Thanks for any help -GK

I am trying to paste a formula in multiple cells and I want the range to stay
the same throughout the sheet. When pasting it obviously changes the range
to the next cell in sequence. I'm using the following formula
=SUMPRODUCT(--(A2:A4000=70),--(B2:B4000="Product Type"),C2:C4000) and want to
kep the range A2:A4000, same for column B and C.

I'm trying to create a COUNTIF function for multiple columns and one criteria.

For example, the range is column X, AD, AJ, AP, and AV with the criteria if the cell says "Pending." I'm trying to create a summary to count how many cells in each of those columns says pending. Also, the summary is on a different worksheet tab than the data. I've tried a few different formulas, but none of them seem to work.

Any ideas?

I'm still learning how to use all the functions in Excel, so bear with me!

Thanks for the help!

Kat

Hi Folks, I have the following formula in a workbook called Quotes. =Customers.xls!Company_Heading. (Customers.xls is open) If I alter the cell called Company_Heading in Customers.xls the cell in Quotes updates correctly. I have created a named range in Quotes called Customers, and it contains Customers.xls.
If I re-write the formula as =Customers!Company_Heading it does not generate any errors but also doesn't update any changes.
There's also a formula involving Vlookup to a workbook called Price List.xls using a named range called Prices.
When Quotes runs the macro involving the two formulae it brings up a dialogue box asking me to Update Values:Prices , which I do. (It also did that when I first altered the formulae) It doesn't ask me to Update Customers.
So, to put it simply, Is my syntax correct? Why do I get the Update Links message, and how can I avoid it? and why doesn't the Company Heading bit update itself?
Any clues gratefully received

I am attempting to create a basic HR database using excel. I have two seperate worksheets and each contains different data (one is job position information, whilst the other is personnel information). Both are defined by dynamic ranges "Pers_Data" and "Pos_Data". I was hoping to use pivot tables to bring the information together to create various reports.

When I use multiple consolidation ranges in the pivot table wizard & enter the above dynamic ranges I would have expected all of the column headings from both worksheets to appear in the layout options for the pivot table. Instead I only get buttons for "Row" "Column" & "Value". The resultant pivot table is of no use.

Am I missing the point here or can you only do this if the column headings in the different worksheets are the same? Also, what is the "Page fields option" and "item labels" on the pivot table wizard step 2b meant to do?

Any help would be most appreciated.
Troy

Hi all,

I m trying to create a combo box which after selection of an option, a chart will be displayed accordingly to the selection. However, I am experiencing a problem whereby I m unable to use a range of values (Multiple range) for the chart. Can anyone help with this problem? I used the code:


	VB:
	
=OFFSET(K6,0,$H$1) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thank you in advance.

I am a teacher trying to set up a school level data spreadsheet that will be used for multiple data drops. I want a formula that will pull data based on different criteria (i.e. race, services, etc.). All of this data is located on one sheet in the workbook, then I want the formula to look to a different sheet to return the data that fits the first argument.

For example, I want to report out how many latino students (sheet 1) are reading on level (sheet 2).

Hello,

Is there a trick when using multiple formulas in on cell. I need to use a sumif formula and a vlookup formula. Which do I use first? Does it matter?

I have tried both ways, but it just doesn't seem to work. Any suggestions?

Thanks,
Michael

Hi

How do I reference multiple ranges using named ranges?

Instead of selecting:
Code:
I want to utilise named ranges i.e. instead of AP2 & AS2 I want to use 
Code:
and 
Code:

I tried the following with no luck:
Code:
Any ideas?

I am trying to use named ranges in an array formula and it won't work. Below
is the current array formula as entered in cell E8.

{=D8+SUM((Replacements!$C$3:$C$15="NE-777-IUC")*(Replacements!$K$3:$K$15="March")*Replacemen ts!$N$3:$N$15)}

Following are the named ranges from another worksheet withing the same
workbook.
Name range for Column $C$3:$C15 = RBU
Name range for column $K$3:$K$15=RMO
Name range for Column $N$3:$K$15=RLeas

How would I rewrite the array formula to use only the named ranges? Any
help will be greatly appreciated.

TIA
Jan


No luck finding an answer? You could always try Google.