need a mix of the vlookup and sumifs functions! (well I think at least!)
I want to find Cell A in Column 1 AND Cell B in Column 2 (like a sumifs) and then return me a value in a range (as a normal vlookup would)
Basically a vlookup matching two cells/columns rather than one
I am trying to solve the problem I am having for my weekly report. Currently our Reporting has to be done Via Excel, with no reporting consule to have dynamic ability. I am trying to put a formula table together so all my manager has to do is dump raw data and the calculations take care of the totals for the table. I am running into the issue of when trying to return certain data at a certain date. So here is what I have and what I need (If it can be done without changing the formula each week)
For the MTD calculation I have:
=SUMIFS('Raw2'!$L$2:$L$902317,'Raw2'!$O$2:$O$902317,"=2013",'Raw2'!$M$2:$M$902317,"=11",'Raw2'!$N$2:$N$902317,">
=1",'Raw2'!$N$2:$N$902317,"<=31",'Raw2'!$B$2:$B$902317,"=0000013")
The raw data wont have information past the current day of the month so the <=31 allows for the current month to calculate correctly.
Where I am having trouble is the Previous Month Formula:
=SUMIFS('Raw2'!$L$2:$L$902317,'Raw2'!$O$2:$O$902317,"=2013",'Raw2'!$M$2:$M$902317,"=10",'Raw2'!$N$2:$N$902317,">
=1",'Raw2'!$N$2:$N$902317,"<=24",'Raw2'!$B$2:$B$902317,"=0000013")
This returns the data, but I have to change the "<=24" every week to the date that I want up to.
I have tried =eomonth(today(),-2+1) but that is not allowed as the Criteria.
SUMIF and SUMIFS formula, where in I want to set criteria in the formula as greater than or less than or equal to value derived from another formula. I am inserting the following formula but excel is not allowing me to enter the formula.
=SUMIFS($C$22:$C$30,$E$22:$E$30,>=LEFT(L21,3),$E$22:$E$30,<=RIGHT(L21,3),$A$22:$A$30,G22)
I'm trying to write a macro that looks up Column A so that each time it finds "Agent" as the first word in a cell, it takes that whole cell and copies it to another sheet.
I tried creating an array function that combines the If, Vlookup, and Right() and Left() functions, but no luck. Ideally, I would want this as a VBA macro.
IN NEED HELP WITH vlookup FUNCTIONS:
1-st table
ABCDEF12221.985
2-nd table
>40
0.1
40
I have a spreadsheet with over 20,000 rows. From another spreadsheet I need to find if any of a list of 90 customers are within the first, using a simple IF and vlookup formula which comes back yes or no.
I am using: =IF(A2 = VLOOKUP(A2,ihcust!B:B,1),"YES","NO")
However, although this works when they are spelt exact in both sheets if they are not it will say no.
eg. If I was Looking up "Joe's Icecreams" from the list of 90 but in the other sheet it is listed as "Joe's Icecreams (admin)" it would not recognise it and come back no.
Is there away to get around this problem
I have made an input form which the user can input the data of person they wish to sit.
I then used Vlookup to put this data on to a smaller table.
This will make more sense on this download of the program.
http://files-upload.com/199899/MainInput.xls.html
What my problem is....is that i have too rooms and i needed excel too look at the cell see that they want to sit in room one then it looks at what table they want to sit at then looks at the seat they want to sit at then put them there.
In sheet1 I have name(column A) and value(column B)
In sheet2 I have name (column C) and key(column D)
In sheet 3 I have results
Example ( It is just a total nonsense example, the real data is net inflow of some funds that are unique)
Sheet1
House 1000
Car 1500
pet 2000
Sheet 2
house1
car1
pet2
So in the sheet 3 I want to put a formula that is capable to sum all values of the sheet2 if the name has a key of 1in the sheet2, the key columns has values of 1 or 2
I think in SQL will be something with join and group by with having clause.
The result will be 2500 in the sheet 3.
What I tried: My attempt is to do something like this code ( using array functions).
I have drop down fields using a vlookup to grab the corresponding data in a colums next it which is being concatenated into a one big cell. The problem is a set of other columns that I need to pull data from but its dependant to a previous column. In the attachment you see which ever region is selected the following column data is grabbed and the same goe for title. Now when the location is selected the info in its column should be selected that corresponds to the row that the specific title is on. example
if selected: North America>Secretary>Texas
results: A Crazy Mix->;typical,Notepad, pen,square dance
I am trying to obtain price of an item using vlookup() function on the identifier “0003128” (7 digits) stored with “custom” format “0000000” in one worksheet. However in other worksheet, the identifier is stored as “000312” (6 digits) with “text” format without the last digit ‘8’. I tried using the left(A,6) function on “0003128” but instead of returning “000312” it returns “3128” and I’m unable to use the vlookup() function.
View 6 Replies View RelatedXL 2003
I want to combine these three functions
=VLOOKUP(e4,AGTNAME!$A$1:$E$3086,5,0)
=LEFT(E4,FIND("/",E4)-1)
and iserror
I need a formula that will bring up the Model type of a product based on its Material code. All the models I need to generate have a "root" number in them (the first 6 digits define model type). This is what I need to fill out:
View 2 Replies View RelatedI am aware of the following topic in the VBA Help file:
"Using Microsoft Excel Worksheet Functions in Visual Basic
You can use most Microsoft Excel worksheet functions in your Visual Basic statements. To see a list of the worksheet functions you can use, see List of Worksheet Functions Available to Visual Basic.
Note Some worksheet functions aren’t useful in Visual Basic. For example, the Concatenate function isn’t needed because in Visual Basic you can use the & operator to join multiple text values."
And I'm aware of how to call Excel funcitons from within VBA; e.g., answer = Application.WorksheetFunction.Min(myRange)
However, not only are some Excel functions not useful; the fact is they cannot be used because VBA has a native function that does exactly the same thing and you have to use that native VBA function to achieve your goal. It is these overlapping functions that I am especially interested in. I want to know what I should use directly in VBA and what I need to go to Excel for.
I'm trying to find the maximum over a set of conditional sums, preferably using an array formula or some other single cell solution. My experience with array formulas so far has been to copy things off the internet and modify slightly to my needs.
Here is how my data is set upA
B
C
D
Trait1
Trait2
Value
Trait 1 to Max over
Cat
LongHaired
3
Cat
[code]....
The "Trait1" I am summing over will remain constant for each refresh of the spreadsheet. I want to return the max value of the sum over each "Trait2" that falls into the "Trait1" category.
In this example for Trait1 of "cat" I would return 8 (5+3) that corresponds to LongHaired cat.If I were writing straight SumIf and then Maxing over I would write
Code:
=SUMIF(C:C,A:A,$D$2,B:B,"LongHaired")
for each unique Trait2 and then Max over the set of outputs.
I am using a SumIfs formula and I want to add a criteria that only sums the Sum Range when it is between two given dates (01/01/2013 and 31/12/2013).
View 2 Replies View RelatedUsing TODAY() in SUMIFS
so, this formula works as expected:
I've been looking for an alternative to SUMIFS as I have a sheet I need backwardly compatible with Excel 2003. I've looked at SUMPRODUCT which I understand is a good alternative to COUNTIFS but does not allow for summing. I've now moved on to looking at an array formula I picked up from an internet search, but it's not working properly for me. The crux of my Excel 2007 formula is:
View 2 Replies View RelatedI have the following formula in Col C
=SUMIFS(C$275:C$379,$A275:$A$379,{"Idle Time","Oil stock *"})
The criteria "Idle Time" & "Oil stock losses" are in Col A.
I would like to add the values in Col C based on "Idle Time" & "Oil stock losses" which are in Col A
The formula =SUMIFS(C$275:C$379,$A275:$A$379,{"Idle Time","Oil stock *"}) adds the idle time value correctly, but ignore the values of Oil Stock losses
I have the formula below which sums range dv77:dv81, if dy77:dy81 is not an error. Works perfectly.
={SUM(IF(ISERROR(DY77:DY81),"",DV77:DV81))}
However I cannot seem to modify it correctly to work with a sumifs formula. Could someone please show me how it might work?, or a workaround?
For reference the sumifs would sum range(DV77:DV81), if range(E77:E81) is "Neutrogena" and range (A77:A81) is "9".
I am trying to create a master spreadsheet to sum up other tabs for the number of funds that are going operational by month. The date is formatted at "January 31,2013".
See attachment as an example : Client Schedule Example.xlsx
Having issues trying to do a sumifs function using multiple criteria to get a final total. I want to sum a list of values that have different transaction types from a transaction table. The only working way I have found is to do a sumif and create a CSE formula for it and drag it down and sum to the total. I am looking for a way to sum the the entire list of transaction types I need from the transaction tab and get to the total value rather than have to use a CSE and then sum. Basically I want to take out the sum of the CSE results....Is there anyway to do this?
View 5 Replies View RelatedI have the following figures in my spreadsheet and I have been trying to use SUMIFS. However, I don't want to count values in a continuous way, rather than that, I need to count the values ONLY until the number that is being counted repeats.
Example: Spreadsheet from Column Q1 to Column Q6:
Q1 Controlling
Q2 8
Q3 8
Q4 8
Q5 8
Q6 8
On this case, the Excel would count the number 8 five times in this array. However, If there were more number just below the number 8, it would count them as well and that's not what I am looking for.
Q1 Controlling
Q2 8
Q3 8
Q4 8
Q5 8
Q6 8
Q7 5
Q7 8
What do I need is not to count them when the number being counted has stopped to appear eventhough it can be found at Q7.
For my needs the answer should be that the number 8 was counted 5 five times rather than 6.
Here is a sample of the formula I am currently using.
Formula:
[Code]....
This works perfectly for what I was needing but I am adding more criteria and running into a problem. I am rolling together usage for parts with different part numbers but the same function in order to reduce SKUs (as well as capture replaced SKUs) needed. These are located in Columns A:G. what i would like to add is more conditions to where the parts are going. Currently I have one location located in cell B2. To analize my usage further, I want to add another location in C2. So i want my formula to look like this.
Formula:
[Code] .....
when I use this it still only adds up with what matches in B2 regardless of the value in C2. Conversely, I can get the formula to work if I take out the extra part numbers.
Formula:
[Code] ....
Attached is a spreadsheet. The first worksheet is a summary of the data inputted in the second. I am trying to total the "total spend field depending on whether it is "Extra Works", "Spares & Materials" or "Other" which works fine. I would now like to sum depending also on the month that it was spent.
Order Tracker 2.xlsx‎
Using the SUMIFS function, I could successfully get the expected results from extracting data from 15 base separate excel files (from where the data will be extracted). However, whenever I am re-opening my master sheet(where I want the data to reflect) it gives me #VALUE!
Only after opening all my 15 base sheets, my master sheet fetches data.
I am trying to do a formula that will add two different sums and so far I have this:
={SUM(IF('FAC Open POs'!$F:$F='Cash Waterfall'!$B6,'FAC Open POs'!$V:$X))}+SUMIFS('Cash Report'!$O:$O,'Cash Report'!$S:$S,'Cash Waterfall'!$B6,'Cash Report'!$T:$T,'Cash Waterfall'!G$4))
But I keep getting an error. I know that the formulas apart work, but I keep getting an error with trying to use an Array formula like this.
Following the syntax of the SUMIFS formula --> Can "criteria_range1" be the same as "criteria_range2" ?
It didn't work for me and I got the result as 0.
I am trying to use the sumifs function to add a column that is in pounds but with multiple criteria.I am trying to add a price field but between two dates (between the beginning and end of each month) another criteria from a range of cells I have copied the formula I have written below
=SUMIFS(Sheet1!K:K,Sheet1!B:B,">="&I17,Sheet1!B:B,"<="&J17,Sheet1!W:W,"="&M17)
try to explain a little column K is the column I need to sum column B is the date field that I need to extract i.e. 1st to 31st January (I have put these dates in a cell that correspond to I17 and J17)
then the last bit column W is another criteria that I need met, this works for just one cell however if I try to add another criteria in another cell it doesn't work after M17 I add to the formula ,Sheet1!W:W,"="&M18)
Attached file should be self-explanatory.
I want to be able to set sum range using lookup or match.. For example if I change B2 value to Nov-13, I want it to sum column K.
I'm having a complex issue that I've tried solving with =SUMPRODUCT(SUMIFS( and regular old =SUMIFS(, but all to no avail. Here is the issue at hand:
I need to look up three criteria, one of which is a text column.
Column A - Qualifier, is this a qualified sale (yes or no)?
Column B - Salesperson, who had the sale (Jacob, Charles, etc)
Column C - Date, when did this happen (2.2.14, 2.14.14, etc)
I need these three criteria to return a sum of Column A - how many are a yes. The formula that typically works for me for such a big sum, when only numbers are involved for the summing, is:
=SUMIFS(A:A,B:B,"Salesperson name",C:C,"Coordinating date")
I've uploaded how the final product should look across.