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))
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.
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 RelatedI'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.
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.
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.
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)))
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??
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.
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))
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
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.
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 RelatedIm 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 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] ...........
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."
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 RelatedI 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 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 RelatedI 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.
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?
I'm working on a sheet where by I need to reference multiple columns of information to calculate a total.
Such as (simplified example of my real issue):
__A__B__C
1 20 AP1 C30
2 25 AP1 C30
3 35 AP2 E10
4 50 AP3 E10
My requrement is to only add values in column A wich have corresponding column B="AP1" AND column C="C30"
I've tried the following and keep getting errors:
=SUMIF(AND(C1:C4,"C30"),(B1:B4,"AP1")),A1:A4
I'm trying to do this:
=SUMIF(B5:B12,OR("=D*","=?D*"),D5:D12)
It returns zero each time.
(BTW,
=SUMIF(B5:B12,"=D*",D5:D12)
and
=SUMIF(B5:B12,"=?D*",D5:D12)
both work just fine and return non zero sums.)
So my question is,
Where do I put the OR, or is there another way to do this?
I have a workbook which I am trying to clean up and make more efficient (in terms of formula calculating time).
In it are examples of array formula such as this one (which work correctly):
{=SUM(IF(Expected!$A7:$A894=5,Expected!$K7:$O894))/3}
From past readings here, I have heard that the array {SUM(IF(...} is slower in calculating than the SUMIF(...) equivalent.
As such i tried to convert is to:
=SUMIF(Expected!$A7:$A894,5,Expected!$K7:$O894)/3
This gives a different answer though. I thought for a single criteria they would be equivalent.
Q1: What am I doing incorrectly above?
Q2: Is there a more efficient version of the array formula I could use (SUMIF and otherwise), so I can have some options availible.
I got a column A containing a alpha-numeric data and want to sum up the numeric value of each cell which begins with "t" and have the sum divided by 486 (i.e. (120+230+25)/486). I tried the following formula but to no avail.
A
t120
t230
m45
m30
t25
My formular: sumif(mid(a1:a5,2,3,">0"))
I have worksheet of data. I have another worksheet that is used just for a summary. This data is mapped to a "Job number" (1123646, 1256974, etc.) The first two numbers determine what area it is referring to.
Each column represents a different amount (month to date cost, month to date profit, year to date cost, year to date proift, etc.) I need to summarize each column for each area and display the result on the other worksheet.
I can't use exact ranges like = sum(a9:a31) b/c that range will change each time the worksheet is used. I thought maybe using a loop, but then I got stuck and don't know where to go from here. Here is what I have so far.
Sub SumProfit()
Dim C As Range, sh1 As Object
Application. ScreenUpdating = False
Set sh1 = Sheets("JB-PRF")
Set C = Range("A9")
I don't want to abuse LOOPING or VBA for that matter, but I don't know of any other way to accomplish this seeing as how the ranges will not be the same each time. the shee that the data should be extracted to is named SUM-PRF
I am trying to sum the total of 10,000 lines in column B if column A is 0. I cannot get sumif to work. do I use a different formula?
View 6 Replies View RelatedI know this is a dumb question but I can't get a sumif to work in vba. I want the actual formula in the cell, not just the output value.
This is my most recent attempt:
VB: Range("C2").Formula = "=SUMIF(Z7:Z12000, >0,N7:N12000)"
I get Run-time error 1004, application defined or object defined error.
Why doesn't this work?
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 ?
Column A has employee Numbers . ( May repeat , not necessarily specific no.of times )
Column B has date of Joining
Column C has effective date ( There is certain amount to be paid to that employee , from that date until it is revised to a new value )
Column D to - Column H has Months .(Jan-2013 to Jun 2013 ) with the amount to be paid as stated above.
What is the total amount to be paid for an employee considering the updated values .
As shown in the excel , coloured cells are to be summed up for an employee (ex:- Emp.No 1 , Emp. No 2 )
Emp. NoDate of JoiningEffective Date Jan-13Feb-13Mar-13Apr-13May-13Jun-13
11-Dec-12 1-Dec-12100110000400010003000 500
21-Jan-131-Jan-13102112100484012103630605
11-Dec-121-Apr-1300010003993666
32-Feb-131-Feb-13014641585614644392732
11-Dec-121-May-1300005315886
43-Jun-131-Jun-1300000974
[code].....