SUMIF - 2 Conditions - With References
Sep 20, 2005
I have the array formula:
=SUM(IF((Sheet1!$Y1:$Y5809="26")*(Sheet1!$AJ1:$AJ5809=CREDIT!$A4),1,0))
It works fine except I want it to reference the cell that the number 26 is in rather than having to have it in quotes, i tried this, but don't get the same answers as this way. The reason being is more conditions may be added in the future which would mean for example the 26 could change to a 34 etc so i rather not have to have someone manually change that number in the formula and just have it refence a cell.
View 9 Replies
ADVERTISEMENT
Jul 29, 2009
I am very comfortable with using sumif statements although I have tried it link two data from 2 different sheets and I keep getting returned #VALUE!
I get a message "Unable To Read File" in a pop up box before fully opening.
View 9 Replies
View Related
Aug 15, 2008
I am trying to use conditional formatting in Excel 2003 to indicate when certain tasks are overdue (by highlighting the cell in red). My problem is that I have multiple criteria. If my line item is a "priority" item, it is considered aged if not resolved after 2 days; if the line item is "routine", the item is not considered aged until after two weeks (14 days). My goal is:
1) I would like the "status" cell in column D (which says "open" or "closed") to turn red if the item is aged.
2) Aging depends on the "priority" status in column A .......
View 9 Replies
View Related
Nov 28, 2013
I have 2 excel files (both .xlsx) and in the first file i am creating a reference to cells in the 2nd file using SUMIFS function. The 2nd file has 275,000 rows of data. I can create the formula correctly, however when i save the file, it just crashes every time (says not responding). If i create simple reference (=A275000) then its fine so im not sure what the issue is.
View 1 Replies
View Related
Mar 7, 2007
Below I have a formula which for some reason or another I cannot get to work.
It is an array formula and it as worked before.
I am not too sure if the reason for the formula not working is because there is a formula in each of the columns that it is looking up.
And I keep getting the result of #N/A.
The formula which I am having problems getting it to work is;
=SUM(IF($D$3:$D$43=$B$57;IF(I$3:I$43="~*~";1;0);0))
View 9 Replies
View Related
Feb 17, 2009
I have two columns with numbers and want to build the sum of them with a condition.
These are stock prices. So prices go up and down and I can go long or short.
I have a column that indicates if I went long (l) or short (s).
If I go long (l), and its a winner, I want to have the sum of Exit Price - Entry Price.
If I go short, and its a winner I want to have the sum of Entry Price - Exit Price.
If I go long (l), and its a loser, I want to have the sum of Entry - Exit.
If I go short (s) and its a loser, I want to have the sum of Exit - Entry.
Overall I just want to make sure that the sum has the right algebraic sign (+ or -), depending on whether it was long or short and whether it was a winner or a loser.
View 9 Replies
View Related
May 25, 2007
I need to sum amount if several criterias are met. I have attached an example of the report I need to reference to. If column A=724 and column B=ZZZ I want to sum on the amount in column C next to ZZZ. The problem is that ZZZ is not always in the same cell, or it's not even in the report if it has no amount. Is it possible at all to sum on those complicated criterias,
View 5 Replies
View Related
Jan 4, 2007
I'm trying to get a sum only if 2 conditions are met.
Column A has dates (ex. 01/02/2007) in a random order. Column B has a location in a text format (ex. London). Column C has a series of values formatted as [h]:mm.
What I'm trying to do is get a sum of the values in column C where the corresponding values in columns A and B = the date and loction I specify. I've tried...
=SUM(IF(('Jan ''07'!A$2:A$750="1/2/2007")*('Jan ''07' E$2:E$750="London"),'Jan ''07'!J$2:J$750))
but it's returning zero values regardless of the criteria being met or not.
View 12 Replies
View Related
Jan 6, 2009
I have 4 columns, A B C and D. They are a mix of formats.
A= Name (text)
B= Date
C= Code (text)
D= Number
I need a formula that gives me the total from column D, provided conditions for columns A, B and C are met. The date condition is month only, so I'm using MONTH(1) for January etc.
I can find a formula if there are only three columns total (using SUMPRODUCT), but not 4.
View 14 Replies
View Related
May 31, 2006
My book is telling me that I can't use multiple conditions with a SUMIF
statement but other sources are telling me I can. Either way, it's not
working!
My situation:
Look at data in $V$2:$V$144 (range = "group"). If cells in "group" =
"Broth" then look at $Y$2:$Y$144 (range = "bldg"). If "bldg" = "1" then sum
corresponding cells in $R$2:$R$144 (range = "fcst").
This is one of my attempts - feeble...
=SUM(IF(group="Broths",IF(bldg="1",fcst,0)))
View 9 Replies
View Related
Jan 25, 2012
I am trying to use multiple "ifs" and then summing the cells that obey my conditions.
Let me give an example:
Column A - place
NY
Chicago
Los Angeles
San Francisco
Column B - object
gate
keyboard
pencilcase
cellphone
[code]....
I want the ifs and sumifs to:
Check if place = NY
Check if object = keyboard
Check if condition = failure
and sum the prices when all of the above are true.How do I do it??
View 4 Replies
View Related
Feb 8, 2010
I am really stuck here and need help with a formula. I have attached a sheet visuals. I want to add up all the numbers that contain two specific accounts#'s. One account is directly across from the number, where I can use this formula very easily: =SUMIF(Q1:Q288,AF245,Z1:Z294)
The second account is in a different column, but it is not directly across the number. It is five cells below. So basically am having a tuff time applying these two conditions, where one condition does not directly align with the data I want to add.
View 4 Replies
View Related
Mar 7, 2012
I am trying to write a Sumif formula based on two conditions. I want to sum the data in column AK if the data in column D = BUD12 and the data in column E = US Custom. I have written the following formula but am getting a #VALUE error.
=SUM(IF($D$25:$D$700="BUD12",IF($E$25:$E$700="US Custom",$AK$25:$AK$700,0),0))
View 6 Replies
View Related
Jul 16, 2008
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
View 9 Replies
View Related
Feb 20, 2014
I work for a farm where we receive fresh produce from harvesters daily. I created a workbook with worksheets(ws) for my daily inventory counts according to product type and separate worksheets for each day of the week showing the orders shipping for that particular day.
I collaborated the data from the inventory count ws and the ws for each day into one ws called Harvest. I am trying to figure out how to keep a running total of what needs to be harvested vs. what is in inventory so that I can show my harvesters what new product is needed to fill orders for today forward, after subtracting what is available in inventory, and showing the amount shipped for past days . Another part that's throwing me is that harvest brings in new product daily and sometimes they bring in a surplus.
Here is an example:
Lets say today is Tuesday and my Inventory ws shows we have 50 kale in inventory - cell D10
Monday ws shows an order for 50 kale, cell D14
Tuesday ws shows an order for 100 kale, cell D14
Wednesday ws shows an order for 100 kale, cell D14
Thursday ws shows an order for 100 kale, cell D14
On my Harvest ws, Since today is Tuesday, how would I keep the Monday cell showing 50 kale as the amount shipped on Monday, Tuesday cell showing we need 50 kale to be harvested to add to the 50 in inventory and fill the order of 100, Wednesday cell showing that we need 100 kale harvested, and Thursday showing that we need 100 kale harvested?
If Harvest brings in a surplus of 100 kale on Tuesday so we have 100 kale in inventory on Wednesday how do I get the Harvest ws to now show that Monday cell should still show 50 kale shipped, Tuesday cell should show 100 kale shipped, Wednesday cell should show that we don't need any kale harvested since we are using the 100 surplus in inventory, Thursday cell should show we need 100 kale harvested.
View 14 Replies
View Related
Jan 14, 2009
I would like to have a formula in one cell that finds records on another sheet that meet certain criteria, and produces a sum of the total quantities associated with that record. The attached workbook has more details as to what I am trying to do.
View 2 Replies
View Related
Mar 17, 2009
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
View 9 Replies
View Related
Apr 4, 2014
I am trying to use sumif formula to add sales forecast based on three conditions but i also want to add the revenue for current month which i have but for the next one months as well as two months plus.. this will change based on the current month.. below is what I am using for the current month..
=IF($B$3=Reference_Data!E2,SUMIFS(Current!$K:$K,Current!$G:$G,"Yes",Current!$C:$C,$B$1,Current!$E:$E,$B$3),
IF($B$3=Reference_Data!E3,SUMIFS(Current!$K:$K,Current!$G:$G,"Yes",Current!$C:$C,$B$1,Current!$E:$E,$B$3),
IF($B$3=Reference_Data!E4,SUMIFS(Current!$K:$K,Current!$G:$G,"Yes",Current!$C:$C,$B$1,Current!$E:$E,$B$3),
[Code] ...........
View 2 Replies
View Related
Jul 23, 2008
I'm trying to sum the values in a column if 2 different conditions (in 2 other columns) are true (so I'm evaluating 3 columns total).
For example, if I had a list of the prices of all the cars available at a dealership (each car listed on a different row), and I wanted to find the total cost of all toyota celicas listed, how would I do this?
Basically, I'm trying to create a formula that says "if the value in the make column = toyota AND the value in the model column = celica, then sum the related values in the price column."
View 9 Replies
View Related
Oct 22, 2009
I have a number of statements within the Sheet Event Code (Excel 2007). Three times lately I have added a column and had to go back into the code and find all of the references that needed changing to reflect the new column.
I have been working on this for a couple of days and even tried EE, but to no success.
I have read that Defined Names / Constants should be used as often as possible, but even trying that, the VBA code errors out or "hangs up". Even within Bill Jalen's book (VBA and Macros 2007), there is nothing that addresses this, especially using Intersect.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
On Error GoTo mEnd
Set rng = Sheets("Log").[F14:F10000]
If Not Intersect(rng, Target) Is Nothing Then
If Target = "" Then
With Sheets("Log")
View 9 Replies
View Related
Feb 10, 2014
I set up formulas to count text characters in a range of cells. I'm tracking attendance and payments for a small yoga studio.
All I need to do is count "Y"s for prepaid attendance and "DI"s for drop-ins. I have the formulas working but they are absolute so inserting a row will break my sheet.
=COUNTIF(E14:Z14,"*Y*")
=COUNTIF(E11:Z11,"*DI*")
View 1 Replies
View Related
Nov 25, 2007
It is suppose to be that if the employee is "FT" and has worked >=4 years the return is 15. But if the employee is FT and has worked 2 years but less than 4 years then it is suppose to return 10 (these are days off) Or if the employee is FT and has worked 1 year, but less than 2 then it should return 5 days off. And all the others in the column get no days off.
I have tried to do it with structured references and with cell references I get a column of zeros!
View 9 Replies
View Related
Mar 2, 2009
I am using the dsum formula to sum some values...the formula in B2 is:
=DSUM(BaseSistemasFebrero,"vlfinf",OFFSET('Planes Entidades'!B$1,0,0,COUNTA('Planes Entidades'!B$1:B$49),1))
The Planes Entidades sheet the data is layed out like this: ....
View 9 Replies
View Related
Oct 25, 2007
I am trying to pull cell values similar to a SUMIF function (SUMIF(range,criteria,sum_range)). For example, in A1 I use a data list created from data elsewhere on the spreadsheet. In the data I created elsewhere, there are 2 columns being used. The 1st column is the information that is being used to create the list and the second column contains specific values (number or text). In the dropdown menu I select an available value (text or number) . When I have selected that value I would like cell A2 to show what the cell directly to the right of it shows from the data I have elsewhere in the spreadsheet as mentioned. I have tried the SUMIF function however it seems to exclude certain values (number or text) and I am not sure what else to use.
View 9 Replies
View Related
Sep 17, 2009
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.
View 6 Replies
View Related
Oct 17, 2007
I would like to highlight cells is two conditions are met:Cell = 0Offset(0,-1)>0I tried the conditional format wizard and entered a formula: =IF(AND($J2=0,$I2>0)) But I keep receiving formual errors, which I understand, because it appears to be incomplete formula. But I am not sure what else I need to add to the formula in the conditional format wizard
View 2 Replies
View Related
Apr 21, 2009
I have many kitchens using the same recipes. I need to distill information down until I've got a summary of how much is being made. Uploaded is a condensed version of the point in the process I'm having difficulty with. This workbook will pull information from 8 other workbooks and give me excatly what everyone made on any weekday.
And from there, with the kind help of this forum, I figured out how to do a SUMIF based on the recipe number. And it summed up all instances of 'Recipe X' being used. However, it continues to SUMIF itself all the way down the page... which is good, because of how recipes are chosen for each kitchen. However, I only need to report one instance of each recipe.
In the uploaded example (and I apologize for the colorful sheet, but it helped me double check what I was working on.) ... I only need to report the PURPLE results elsewhere... the first instance of each SUMIF.
View 5 Replies
View Related
Feb 27, 2012
I am wanting to use these two formulas in one cell. Is there anyway to do this? If "AD3" is 0 I want this =SUM(X3:AC3) and then if cell "AD3" is greater than 0 I want to basically use this formula
=SUM(AH3,X3:AC3)-AD3.
Is there anyway to merge these two formula's?
View 2 Replies
View Related
Jul 31, 2009
What I'm wanting to do is essentially use a reference to update yet another reference.
In more detail, I want to retrieve a value on a worksheet and use that value to alter yet another reference to another worksheet.
For example, I want to retrieve a value (let say its 16 from either a local cell or a cell on another worksheet) and then retrieve yet another value from another worksheet based upon that value (lets say the worksheet I would want is then "Subject 16").
I've attempted various nesting formats of the typical reference formula but obviously have had no luck. Would I need to use VBA or is there a simple solution that I am missing? If I do have to use VBA how should I go about doing this?
View 14 Replies
View Related
Mar 11, 2009
for example, is it possible to check if a Reference to Set a reference to Microsoft Internet Controls (shdocvw.dll) has been enabled in user Reference settings. And if not set, then set it via VBA
View 4 Replies
View Related
