I've noticed recently that a sumif formula will only ever add up 1 column's worth of data. Is it possible to get it to do multiple columns?
For eg, I have a spreadsheet that looks like:
Using the formula shown, I can easily add up values for 'A' codes for one of the 3 columns, but short of adding together multiple sumif's, there must be a more intelligent way of doing it?
I am new to excel and functions I am trying to put together a spreadsheet of computer costs. Basically I am listing various mobos, cpus, psus, etc. I have a column that signifies which mobo/cpu/etc I want to include in the actual total cost of the computer. Currently the only way I can perform the summation is to create a long drawn out calculation, which is a pain if I need to add more rows to the spreadsheet. I want to try to do a summation.
So, the grid looks something like the following:......
I have to SUM vendor totals in my spreadsheet and SUMIF worked perfectly, but only for one column of expenses. I need to sum over fifteen columns but searching told me that SUMIF / SUMIFS will not work. Is there another way?
View 9 Replies View RelatedI'm trying to sumif across multiple columns, only if there is NO data in the first cell of the row. Column A is not in use - B contains some blank cells and some with data. In columns C:AM I want to sum all data in rows where the cells in Column B are blank.
Code:
=SUM(SUMIF($B$2:$B$191,"",C2:C191),(SUMIF($B$2:$B$191,"",D2:D191)),(SUMIF($B$2:$B$191,"",E2:E191)),
(SUMIF($B$2:$B$191,"",F2:F191)),(SUMIF($B$2:$B$191,"",G2:G191)),(SUMIF($B$2:$B$191,"",H2:H191)),
(SUMIF($B$2:$B$191,"",I2:I191)),(SUMIF($B$2:$B$191,"",J2:J191)),(SUMIF($B$2:$B$191,"",K2:K191)),
(SUMIF($B$2:$B$191,"",L2:L191)),(SUMIF($B$2:$B$191,"",M2:M191)),(SUMIF($B$2:$B$191,"",N2:N191)),
(SUMIF($B$2:$B$191,"",O2:O191)),(SUMIF($B$2:$B$191,"",P2:P191)),(SUMIF($B$2:$B$191,"",Q2:Q191)),
[Code] ...........
Im trying to have a formula look at two diferent columns and if they meet the criteria add the third column.
I tried using sumproduct but It wont give me anything but error messages or a zero.
Looks like this
=SUMPRODUCT(A:A="Stewart",(G:G="Fiduciary"),C:C)
Need it to say:
if column A = Stewart and Column B = Fiduciary then add up the amount in Column C
I am trying to do sumif on below
A B C Total that I need to achieve
-520 Check [blank] -520
-321 Check -321 0
Criteria
Sumif B2 have "Check" add A2 then -C2Problem is C2 although shows no value, there is formula that returns Blank Value. See attached file.
I need to perform 2 SUMIF's on 2 columns of data to return a result and I'm not quite sure the best way of doing this. I'll give an example below.
I have 2 columns of data, both numeric and the SUMIF needs to say if H1:H100="10" and also if J1:J100="907". I can perform one or the other but not both.
I'm using the following to return multiple reponses to a criteria. However, it returns the answers in rows. i.e. A1, A2, A3. I want it to return in columns. i.e. A1, B1, C1.
=INDEX(RMList!$A$1:$B$32381,SMALL(IF(RMList!$A$1:$B$32381=Errors!$D$2,ROW(RMList!$A$1:$B$32381)),ROW(1:1)),2)
Im working on a dashboard for my department and its going pretty well. Im stuck on the Project tracker.
On the front page (dashboard tab) I want to see all overdue projects from the Project Tab. I have the Overdue status working on the project tracker so I need some kind of multiple return vlookup for all of the OVERDUE tagged rows. This can be a macro or auto fill formula.. if someone has seen something similar, i would love a link so i can edit it accordingly.
I'm trying to put together a spreadsheet that tracks disc capacity increases, affected by any incoming projects. I've managed to do so for one project, but would like to for up to 10. The way i've designed the solution (i'm sure there are far more elegant ways, but hey) is thus:
A forecast worksheet keeps track of a grand total, taking information from sheets P1 -> P10 (being projects 1 to 10). I am unable to figure a way to add up all the increases from all 10 project worksheets with one succinct formula. What I use so far is: ='P1'!C83+SUMIF('P1'!E82,"=2009 - Q1",'P1'!D82) ..................
I have a dataset in the following manner
A B C D Value
1 0 2 3 D
2 1 3 4 D
6 2 3 4 A
The VALUE field is my problem. I need a formula so that the value field takes the max of the other four columns and returns a string (e.g. 'A' in the column header)
I am trying to write a formula that will give more than 1 return for a vlookup statement.
The info i am looking up is sku code (col a) and expiry date (col b).
There are more than one entry for some skus , but I am only getting returned the first expiry date.
I have a raw data page which contians peoples name and where they charged thier time. My problem is that I only want thier name to appear once. What type of formula do i need to use to make this happen.
Ex
Employee name Project No. WBS Code Hours
John Smith 123456 1 4
Jane Doe 123456 1 2
John Smith 123456 2 2
Jane Doe 123456 2 1
John Smith 123456 3 2
Jane Doe 123456 3 5
At the end I want to do a count to let me know how many people I have charging the job but I don't know what formula I need to exclude duplicate entries.
I need to lookup a Order Number and return all associated Item Numbers. My spreadsheet has about 10,000 lines and these Order Numbers could have as little as 1 or as many as 15 Item Numbers associated. I would like to have a drop down on each Order Number that shows the Item Numbers.
View 3 Replies View RelatedIn this sample below, I need to extract all matches for my look up value. Where I extract the data to is not important because it will be used as informational data in user forms. Basically, I have 670 rows that make up this table...this table is static (never changes). Each row represents a unique combination for achieving the value on the left.
To take it a step further, I would like the ability to deviate from the lookup return...in other words, force the return to be one row down, or one row up (this would satisfy my first requirement as well). I tried offset with vlookup, but I cannot make that do anything useful. I used match to get the row number of the return, but I don't know what to do with that row value now that I have it sitting in a cell in order to accomplish this. I was thinking I could use an offset formula with the cell's value to get this done, but I could not figure out how without doing it in VBA.
In the end, I will present this data on a userform...first will be the actual vlookup returns, then the user will have an option to select the next row down if they want (same look up value if one exists) or up (next higher value).
If possible, I would like the data lookups to be accomplished without VBA.
This is an extremely large working application, (about 17MB so far), lots of VBA coding and logic applied. This problem is a result of a request from the users of this application.
Here is a sample of what I am looking at.....
a
b
c
d
1
monday
tuesday
wednesday
thurs
2
ted
bill
frank
mary
[Code] .......
We use excel for scheduling purposes and each and every day we fill out a floor chart with who is working that day. As it stands now we have to print out a template and hand write all names or type them in and print it out. What I would like to try and do is define each set of names and allow for easy access to auto fill these names. I feel it needs to be a simple process as not all of our managers experienced in excel or formulas. What I would like to try an accomplish is in sheet 1 cell A1 to be able to create a drop list or formula that that would let me return all the names for a given day.
For the purposes of this discussion we can just define each list as the day that is the header ( IE A2:A4 on sheet 2 would be defined as "monday". Also, I already have the names autofilled based off of what the our schedule has. I'm curious if I could create a drop down list with each day of the week as an option, and if they select Tuesday it will then return all the names defined as Tuesday in a column below.And the next day be able to return Wednesday names and so on.
I'm not stuck on it being a drop down list. Maybe if they could just type and =(defined name) in the box above and it would return all the names.
I have tried an =(defined name) and it does return the value but only for the first cell and it has to be in the same row for it to return the name. Which doesn't work because I would like the names that are being defined on a separate sheet.
I have this from FDIBBINS working if data is on column D and E
=COUNTIF($E$2:$E$4,A2)
How would the same count function if there is a column between d and E
as attached ?
trying to add columns using a sumif
The names I'm using are in columns A, H and and J and the figures in B, F and K
Tried the formula belw but wouldnt work, said too many arguments
(A1:A30,E1:E30,H1:H30,J1:J30,"Dog",B1:B30,F1:F30,I1:I30,K1:K30) and tried named ranges =SUMIF(animal,"dog",age) and get value #
I've never quite mastered the idea of passing arguments between subs and functions. I have a project right now where I am writing this same bit of code over and over, for different parts of the macro:
View 4 Replies View RelatedLet's say I have a standard Vlookup formula entered in cell C2
Code:
=Vlookup(A2,Sheet2!$B$2:$C$10000,2,0)
For that formula there may be 100 matches found in Sheet2, each with a different return option and obviously the above formula only returns data from the first found match.
Is there some way to drastically change that simple formula in VBA script to ...
1. Auto fill formula down 100 rows & return all possible return options upon match and not just the first return given upon the first found match
2. Make the lookup value be the A cell in the active row
I'm trying to do the next level of a v-lookup. I have a group of data with a text "flag" on certain rows. On my summary worksheet, I'm trying to do a lookup or a nested index function retrun all of the values with the text flag next to it.
I've been playing around with nested formulas and this is where I'm stuck. Here's what I came up with:
=INDEX($D$1:$E$9,(SMALL(IF($D$1:$D$9=$A$1,ROW($D$1:$D$9),ROW(1:1)),ROW(1:1))),2)
This only works for one row and not for the other rows. Maybe I'm going down the wrong path?
Entering a code in a cell, I'd like the cell next to it to return a list of possibles which the user can then select:
Line Code Material
123456 Apple
Orange
Strawberry
By entering the line code the user get the 3 Materials that has this Line code and they coose the one they want.
The list I was using to look up looks thus:
Line Code Material
123456 Apple
123456 Orange
123456 Strawberry
I've read a bit about combo boxes and lost myself complete, so please use small words for me.
I'm getting cross-eyed from trying to find the source of the error and not having any success. I've successfully used this approach many times, but for some reason I'm getting an error I can't pin down.
The problem: INDEX-MATCH lookup formula returns # NAME error. Formula: =INDEX(Data,MATCH(H8,Date,0),MATCH($I$7,Line,0),MATCH("FPY",Hdngs,0)). I've done my best to verify the named ranges and cell references are correct and have stepped through the help file on the "potential source of the error", including going to Tools >Options and making sure "Accept Labels in Formulas" is checked, as well as having read a number of other posts.
I am doing a SumIf function on some Imported data a follows "=SUMIF(FMEFactors!B:B,Long,FMEFactors!B:B)". Now, this is fine if the columns are imported in the same order but if Column B data gets moved to another Column the SumIF will give false information. I need to find the correct column to do the SumIF on.
View 3 Replies View RelatedI think this is simple, but I can't seem to get what I want. I have three columns.
A: months for a few years (rows 3 - 37)
B: data-set 1 that starts in the middle of the timeline to present (rows 20-23)
C: data-set 2 that starts at the beginning of the timeline to present (rows 3-23)
Now, I need to sum for the previous 12 months at the bottom of each column. For column B, I only have 4 data points, so I want to add the 8 data points for the previous 8 months from column C. How do I do this without just doing a couple sum functions -- is there a function that will evaluate the situation on its own?
I am having trouble with my macro. I have attached excel sheet for reference.
Basically, I want a Macro that Looks up columns B & C of current sheet and matches it with table in the charges tab, and then returns hourly pay in column D.
I have created the macro but it's giving me Run time 1004 error.
Also, my formula is incorrect. Maybe that's why my macro isn't working?
2WayLookup_Macro.xlsm
MR EXCEL EXAMPLE.xlsx
I'm trying to create a sumif or sumifs function that will add numbers from two columns. I've attached an example (semi populated as needed to get rid of some personal data so ignore the ref notes). On the Category Worksheet I'm trying to get column D to sum info from the March Worksheet. I would like the function in the Category Sheet to add Columns F and Columns H from the March Worksheet if they correspond to column A22 on the Category Worksheet i.e, the result of the data I've left on this example sheet should be $20....... sum of F = -130 + sum of H = 110
On the attached example i would like a formula in F2 which sums all the values where column equals "Smith" and column B equals "Golf European". I'm assuming it would involve the SumIf formula but i'm not sure if this is the best solution when the criteria is in two columns?
View 3 Replies View RelatedI have a spreadsheet with two columns, in column A i have text and in column B i have time format (01/01/1900 13:30:00) im trying to pull out information that if in column A says London to sum up the hours in column B is there analysis that would calculate this for me.
Area
Hours
Newcastle
37:30
Newcastle
37:30
[Code] ..........
Have had a search around the web forums for an answer to this and am not getting anywhere.
In column A i have text - apr, may, jun etc
In column E i have general format text - a 6 figure number
in column M i have text - eg 886.97 (a currency, but in text format)
i want to:
where column a=apr and column E=212130 sum the contents in column M