Excel 2007 :: SUMIFS - How To Specify Criteria That Looks At Type Of Formula
Jul 31, 2012
I'm trying to sum over a range (column A) based on values in another range (column B). Column B's cell values are calculated with a simple numeric formula that is either a subtraction or an addition of two cell references. I want to sum all the column A cells for which the corresponding column B cells are determined by a subtraction. Then separately, sum the cells for which the corresponding column B cells hold an addition. It seems that SUMIFS is the function to use, but how do I specify the sum criteria as "a subtraction" or "an addition"?
Windows 7, Excel 2007
View 5 Replies
ADVERTISEMENT
Aug 14, 2013
I have a spreadsheet in version Excel 2007 that is refreshing data from Access tables. Users are needing charts to show progress by month, but are also needing to filter by area and one to filter by a section.
What I have so far is the data being calculated using SUMIFS so that I can pull between dates. I then added two comboboxes so that the user could filter by an area and by section. This is working, except that I have not been able to figure out how to allow the user to select "All" so that they can see all data from on or both of the selections.
View 2 Replies
View Related
Apr 28, 2014
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)
View 3 Replies
View Related
Nov 10, 2013
I'm working on a formula for calculating shipment weight per city it goes to per weight scale of the shipment pricelist. Although we have 4 customers with customer codes which we ship with our own trucks and some customers pick up there goods.
Therefore, my sumifs formula currently looks like this:
=SUM(SUMIFS('Freight SH 13'!$R$7:$R$14176,'Freight SH 13'!$R$7:$R$14176,"
View 1 Replies
View Related
Jan 7, 2014
I am trying to do a sum of data that contains three requirements - The country has to match either the USA or Canada and then it needs to pull for only a certain month (i.e. Jan, Feb, Mar) and then for that month only pull either Airfare, Hotel, Per Diem, or Car Rental. Basically looking for a way to Sum only the specific category for a specific time frame.
View 9 Replies
View Related
May 12, 2010
I need to convert numbers to text in excel 2007. I have done it before in 2003, but can't seem to remember exactly what I used. I may have used some kind of conditional formatting, but not sure.
Here is an example of what I need.
If I type "7203" in a cell I want "Home Repairs" to appear. I have a list of words associated with a list of numbers and want to be able to type the number in a cell and have the word appear.
View 8 Replies
View Related
Aug 2, 2013
I use MS Excel 2007 and I was wondering if it was possible to enter data for certain things once, and have that data copied automatically every time I type the text. For example, if I type Blue Car in a cell and type 4 in the next cell, is there a way I can program Excel to enter that data every time I type Blue Car?
View 14 Replies
View Related
Feb 21, 2014
[h]:mm X accounting (2 d.p) = ????
The two data types i'm using are shown above but i don't know what data type to use as the result.
View 4 Replies
View Related
Jun 3, 2014
Is this is just an example, but the logic should hold true. Data for example is in A1:C5. I want to use a SUMIFS statement that will look at type of Vehicle and Dates. So for the example in cell D1 (I wanted to add only Cars sold between May 1-2), but my current equation = 0, where I am expecting 20
=SUMIFS(C1:C5,B1:B5,B3:B4,A1:A5,A1)
View 5 Replies
View Related
Jun 10, 2013
I have a workbook with multiple tabs that I update every other month with current data. I have one tab for data that I clear each time and copy the updated info into. (That info is copied from another excel spreadsheet, by the way.) On the second tab I have sumifs formulas set up to pull in certain data from the data tab. This setup has worked perfectly until today. I copied my data into the one tab but could not get the formulas to calculate. It looks like the formulas aren't picking up the data. I've checked to make sure the formulas are calculating automatically. I've retyped the formula. I've replaced the =. I tried naming the ranges I wanted the data to be picked up from. I've tried changing the format of the data - general, text, numbers, etc. I'm working in excel 2010
View 2 Replies
View Related
Feb 15, 2013
Excel 2010
I have the following SUMIFS formula that produces the desired results until I get the the last qualifier, displayed in red:
=SUMIFS(JBHAMOUNT,JBHMONTH,C18,BNSFCATEGORY,B20,BNSFLOCATION,"Alliance")
The result of this formula is: #VALUE!
View 2 Replies
View Related
Nov 13, 2013
SUMIFS with less than & more than
I found this thread which works for normal ranges
I want to Sum for Values Greater than a specific date in my formula.
This first one works for a specific date (Relevant reference in red), so say 01/01/2014
Code:
=SUMIFS(Table2[[#All],[Basic]],
Table2[[#All],[Cluster]],Table3[[#Headers],[Company]],
Table2[[#All],[Start Date]],Table3[#Headers],
Table2[[#All],[Perm / Temp / Prof / ETA]],[@Company])
Code:
To Sum for ranges >= 01/01/2014, I have tried this below based on the thread above but Excel does not accept this.
=SUMIFS(Table2[[#All],[Basic]],
Table2[[#All],[Cluster]],Table3[[#Headers],[Company]],
">="Table2[[#All],[Start Date]],Table3[#Headers],
Table2[[#All],[Perm / Temp / Prof / ETA]],[@Company])
View 4 Replies
View Related
Oct 22, 2013
I have a macro-enabled template file in Excel 2007. I would like the user to be unable to save in any format other than macro-enabled. They should be free to choose a path and filename, but not the file type.
I presume this means some VBA code in the before save event, but I don't know what.
View 2 Replies
View Related
Sep 3, 2009
Here is what I have
=_xlfn.SUMIFS('COMMISSIONABLE INTERNAL SALES'!$N:$N,'COMMISSIONABLE INTERNAL SALES'!$A:$A,$A$1,'COMMISSIONABLE INTERNAL SALES'!$O:$O,I$1,'COMMISSIONABLE INTERNAL SALES'!$D:$D,$A2)
I need it converted to something usable in 2003
View 9 Replies
View Related
Oct 6, 2013
I have use excel 2007. In our sheet i have in cell "B3" product name i.e. "abc" , "xyz" etc. That is called sales of goods.
In cell "C3" i have number of quantity
If "B3" is "abc" then only calculate below :
1st option : If product name is "abc" then only calculate (which is cell "B3") & cell "c3" is >=15 & cell "c3" is<19 then message shown as "one mobile free"
2nd option : If product name is "abc" then only calculate (which is cell "B3") & cell "c3" is >=20 & cell "c3" is<39 then message shown as "two mobile free"
3rd option : If product name is "abc" then only calculate (which is cell "B3") & cell "c3" is >=40 then message shown as "three mobile free"
In view of the above which formula applicable.
View 4 Replies
View Related
Dec 13, 2013
I have a spreadsheet that monitors employees rest periods between shifts across a number of weeks I've started writing a very basic piece of code so if the employee has less than 12 hours rest, a message box will appear with a warning:
So far this is what I've done:
Sub RestHours()
If Range("I7") < #12:00:00 PM# Then
MsgBox "Candidate has had less than 12 hours rest since last shift worked. Please find another candidate to work this shift", vbOKOnly, "Unable to Use Candidate!"
Now this does work if I run the macro and then enter the data into cell I7, however I'm now stumped - How can I make it so that this message box pops up automatically when the data is entered into the cell without me have to go in and run the macro?
I also need the macro to run all the way down the I column (I7:I100) and across other columns which will eventually contain the same data i.e. M7:M100, Q7:Q100, all the way across to AT7:AT100?
View 9 Replies
View Related
Apr 7, 2014
VBA to copy entire row in all worksheets in file based on criteria.
step1 Copy tab has criteria in cell c1
step2 search if this c1 value in each tab in column e or g or h
step3 check if column i is yes
then if criteria meet, copy entire row to copy sheet
I came across some similar posts in other forums which can do partial work but not entirely .
Note ; number of rows, columns and tabs varies in file
I am using excel 2007.
View 8 Replies
View Related
Feb 15, 2012
Excel 2007. Is there a way to extract information from cells and rows that constantly move? These rows need to be able to cut & paste, copy & Paste, insert and delete. I have the formula I need to display the data, but with the cutting and pasting etc, my formulas get all messed up.
I have the excel sheet on Google docs. It displays some cells wrong because it does not recognize certain formulas.
[URL]
On the second worksheet, I want the information from columns A, H, M, N, and O to be extracted at a certain time each evening and sent to a worksheet, without sending duplicates. These rows will constantly move which is why my current setup, which is just to display the information, will not work... We decided to try to extract it into a different worksheet or workbook if possible.
View 1 Replies
View Related
Mar 28, 2014
I'm trying to populate a table based on two different criteria in a another table. On is based on a date and the other the number of a unit. I'm trying to get the data in sheets Week 1 and Week 2 into the format in sheet Example. Is there a way to create a series of functions, filters, etc that I could use to create a macro to do multiple sheets or at least a whole sheet at once? I've been trying to think through it with my limited knowledge of filters and functions
Here is the file with an example and I'm using Excel 2007: Example.xls
View 2 Replies
View Related
Aug 9, 2013
In excel 2007 i have three sheets.
In sheet1 : Cell : "D3" : I have find out & show maximum date with two criteria (i.e. code & series) from all sheets.
Also in cell : "E3 : I have find out maximum date with two criteria from particular one sheet only.
I have mentioned comments in attach file.
In sheet name 1112 & 1213 : I have a lot of data approx 40000.
Column A : Invoice no
Column B : Invoice date
Column C : Code
Column D : oano
Column E : Name
Column F : city
Column G : distric
Column H : Series
In yellow highlighted cell i required formula.
View 5 Replies
View Related
Jan 27, 2013
I use Windows Vista and Excel 2007.
So to cut to the chase :
On my spreadsheet, I have 6 main groups of columns, each one comprising 4 columns with their own automated calculations across the range.
Group 1: Cell H5, Cell I5, Cell J5, Cell K5
Group 2: Cell L5, Cell M5, Cell N5, Cell O5
Group 3: Cell P5, Cell Q5, Cell R5, Cell S5
Group 4: Cell T5, Cell U5, Cell V5, Cell W5
Group 5: Cell X5, Cell Y5, Cell Z5, Cell AA5
Group 6: Cell AB5, Cell AC5, Cell Ad5, Cell AE5
Then I have two columns following those: Col AG, Col AI
In each one of those two columns, I am trying to sum up a total value using The IF Function.
In Cell AG5 : the sum of the values of cells K5+O5+S5+W5+AA5+AE5,
ONLY IF the values of Cells H5, L5, P5, T5,X5, AB5 is equal to AG3 (which is a symbol (wingding 3 for capital P), this occurs 3 times out of 6, sometimes twice only, and at random, so I need the sum of 3 cells (or 2) out of 6, based on the above criteria.
Same in Cell AI5: sum of the values of cells K5+O5+S5+W5+AA5+AE5
ONLY IF the values of Cells H5, L5, P5, T5,X5, AB5 is equal to AI3 (which is a symbol (wingding 3 for capital L), this again occurs 3 times out of 6, sometimes twice and again at random, so again I need the sum of 3 cells (or 2) out of 6, based on the above criteria.
Using the formula: =IF(T5=AI3,(W5),0) , I retrieved the value of cell W5 into cell AI5; and when I tried the formula :
=IF(T5=AG3,(W5),0) , in cell AG5, it returned the value 0 which is correct again, as T5 was equal to AI3 but NOT to AG3.
But I cannot work out how to add all of the three values relating to each symbol, independently in each column, as they each occur randomly.
This is the way it looks in the spreadsheet:
Cell H5 Cell I5 Cell J5 Cell K5/ Cell L5 Cell M5 Cell N5 Cell O5/
Symbol (for P) Text Text Value /Symbol (for L) Text Text Value
And so on 4 more times across.
I could of course add it all manually for each line but I would rather have a formula to do it for me as all the other calculations are automated and/or conditionally formatted.
View 9 Replies
View Related
Feb 13, 2013
I have a some data where I need to sum some data based on date range, and dept id's. But the dept ids are parts of various groups. For ex, one group covers about 300+ dept id's. Some of them have ranges (e.g. dept 1000 - 1050) but for whatever reason some dept id's that belong to other groups sometimes fall into the same ranges.
My first hope was one big range, but the overlap issue nixed that. As I mentioned there are some ranges so maybe 100 of them could be grouped into a range but I'd still have 200+ independent dept id's.
I read for 2003 the max criteria for sumproduct is 30 and for 2007+ it's 255. So maybe 2007 might cut it but this doesn't seem like the right way.
Btw, I'm doing this in VBA but I mention sumproduct because I've used it a few times within code via evaluate. Otherwise do I just have a massive string in vba (If deptid = #### or deptid = #### or deptid = #### etc)?
Again, it seems like there should be a better way. I thought about making a master list of all the dept's and sumproduct each one individually and then sum from the ones that fall under group but even then I have to identify the deptids I need for a particular group.
View 1 Replies
View Related
May 17, 2014
I'm using Excel 2007
I have the below data in sheet 1
In a separate column on sheet 2 i want to return the value of the data in ACC1 if the data in DEB/CRED is LC or SC and the value of the data in ACC2 if the data in DEB/CRED is SD
DEB/CRED
ACC1
ACC2
[code]....
View 7 Replies
View Related
Aug 11, 2012
I 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
View 4 Replies
View Related
Aug 8, 2014
I am trying to develop a compliance report with Excel 2007) based upon a simple pass/fail criteria. The subtotals must be tracked both by Device (column) and by Requirement (row). There are macros (not included in the attached sample) that hides both columns and rows. I successfully found an example which I modified to correctly calculates data for a column when rows are hidden (see GOOD function below). However, I'm totally clueless on how to calculate data by row when columns are hidden (see BAD function below). What I'm trying to figure out is highlighted in red in the attached spreadsheet.
GOOD
=SUMPRODUCT(SUBTOTAL(103,OFFSET(B$2:B$5,ROW(B$2:B$5)-MIN(ROW(B$2:B$5)),,1))*(B$2:B$5="Pass"))
BAD (returns 0 and includes a circular reference)
=SUMPRODUCT(SUBTOTAL(103,OFFSET($B2:$F2,,COLUMN($B2:$F2)-MIN(COLUMN($B2:$F2)),1))*($B2:$F2="Pass"))
View 14 Replies
View Related
Nov 7, 2013
I use the following formula in excel 2007 (and it's working perfectly in 2007) but this formula does not work if I work with the same file in excel 2003 ...w why and what I have to adjust?
=IF(SUMPRODUCT(--(C5='sheet 2'!B:B);--(C13='sheet 2'!C:C);ROW(B:B))=0;"not found";INDEX('sheet 2'!D:D;SUMPRODUCT(--(C5='sheet 2'!B:B);--(C13='sheet 2'!C:C);ROW(B:B));1))
(I'm working with the dutch excel version so it might be that ";" must be ",")
View 6 Replies
View Related
Oct 31, 2013
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 Related
Apr 11, 2014
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] ....
View 6 Replies
View Related
May 24, 2014
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
View 13 Replies
View Related
Dec 23, 2013
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.
View 5 Replies
View Related
