Can Add Multiplier In Sumif?
Feb 26, 2014
Here is the Formula.
=SUMIF(***!$F$6:$F$951,B16,***!$G$6:$G$951)
excel screen shot.PNG
This is the page it is referencing to.
PO screen shot.PNG
This is where the formula is. It is in "I16"
What I want to do is Take this and add in the multiplier from column "H" in the "***" page. I need to change the name of that tab, I am not trying to be foul. I just use that short for assembly. It looks bad, but usually, I'm the only one that sees it.
Basically, I want to take the material qnty and multiply it by the multiplier and put that in the box on the po page that accumulates the material.
View 14 Replies
ADVERTISEMENT
Jun 14, 2013
I have a spreadsheet using a SUMIF formula =SUMIF(G:G,A2,D:D) that is working fine.
However, I wish to introduce a "multiplier" column (E). That is, if the corresponding row has a figure in the E column I wish to multiply that figure with the corresponding figure in D.
D E G
6 5 A
5 0 B
4 0 A
A = 6 x 5 + 4 = 34
B = 5
View 3 Replies
View Related
Nov 10, 2007
this formula and have tried various nested ifs and I can come up with a formula that works. What I am trying to do it to is detirmine what my multiplier should be based on a range of numbers. Let me try and explain lets say A1 is a number and B1 is a number and I need the Formula for C1.
If B1 is between 0 and 5 multiply A1 by 2 if B1 is between 5 and 10 multiply A1 by 4. and so on and so on. Is there an easy formula for this? I know it might be trivial but im stumped for some reason.
View 9 Replies
View Related
Sep 16, 2009
B2: =SUMPRODUCT(--(INT('Order Entry'!$B$2:$B$37)=Summary!$A2) )
C2: =SUMPRODUCT( (INT('Order Entry'!$B$2:$B$37)=Summary!$A2) * 'Order Entry'!$A$2:$A$37) / Summary!B2
D2: =MIN(IF(INT('Order Entry'!$B$2:$B$37)=Summary!$A2, 'Order Entry'!$A$2:$A$37))
E2: =MAX(IF(INT('Order Entry'!$B$2:$B$37)=Summary!$A2, 'Order Entry'!$A$2:$A$37))
The overall picture is an order tracking sheet that has start times in column B, end times in column C, number of jobs in an order number in column D and the processing time in column A. On the Summary sheet I have dates listed for each workday.
Next to these dates I am wanting a formula that will traverse through column B of the Custom and Order Entry sheets and provide the number of orders and average processing time that match this. There can be multiple jobs per order number so it needs to order the Sumproduct by column D for each.
View 4 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
Jan 30, 2014
We do some discounts for clients where we offer discounts on quantities. I have a column of figures and I can't for the life of me figure out how to add each range. I have the IF statement down to say, IF within this range THAN multiple by, but it doesn't add them together.
Example:
0-15 Items = Free
16-200 Items = $.75 each
201-500 Items = $.50 each
501+ = $.25 each
So I have =IF(A1<16, A1*0, IF(A1<201, A1*0.75, IF(A1<501, A1*0.5, A1*0.25))) Assuming "A"column are my amounts
I want to add each range. So for 600 pieces, the first 15 are free, 16 thru 200 are .75 ea PLUS the $200 thru 500 are .50 PLUS the remaining 100 that are .25
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
Jan 14, 2009
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
View 5 Replies
View Related
Oct 12, 2005
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?
View 14 Replies
View Related
Apr 4, 2009
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.
View 9 Replies
View Related
Oct 19, 2009
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"))
View 9 Replies
View Related
Feb 18, 2006
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
View 9 Replies
View Related
Jan 18, 2007
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 Related
Nov 26, 2013
I 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?
View 2 Replies
View Related
May 5, 2014
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 ?
View 1 Replies
View Related
Dec 4, 2013
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].....
View 1 Replies
View Related
Dec 15, 2008
I am writing the sumif function in F2 but it is not working correctly..
View 14 Replies
View Related
Jan 5, 2009
I am having a problem with a formula I think I should be able to get correct but not sure if I am able to do so. On the attached file I have a filter on the TIER's for "Is grater than or equal to 5". What I need is a SUMIF formula that will take into account the filters. This formula needs to separate out between "GER", "IRE" and "UK" in cells C37, C38 and C39.
I have a subtotal in cell C35 which gives me the subtotal of all countries but I'd like to be able to have the subtotal separated out between the 3 countries and also still have the ability to manipulate the data so I could select different TIER's or a range of TIER's and Cells C37 - 39 automatically update themselves.
View 3 Replies
View Related
Jan 8, 2009
I have an entire excel column which was filled-in with values ranging from 1000 to 40000. What I was trying to do is to just sum up the all the values which are between 9000 and 20000. I tried using the sumif function =sumif(and(A5:A40,">9000"),(A5:A40,"<20000")) but it does not work, it says that the formula that I typed contains an error.
View 4 Replies
View Related
Jan 23, 2009
I am trying to use SUMIF as part of an IF statement, What I am trying to do is, for example, if a column has two values M and P, then SUMIF only those rows that have a corresponding M, and not calculate the P's.
View 9 Replies
View Related
Feb 6, 2009
I am trying to take a census by date and name for every half-hour using the attached data. So for example, I would like to know how many people Roger had that walked-in and walked-out for each date on the spreadsheet and during what hours. The census would be every half-hour and not double count times. I could actually deal with just the min and max time for the day (by date and name) and having some indicator (say a one or an X) in the cells when Roger had at least someone anytime between walk-in and walk-out. It's kind of like getting a "time worked" based on the data that's provided to me. So I know how many hours Roger worked by day.
View 4 Replies
View Related
Apr 17, 2009
I am trying to used the sumIF function in refer to a particular value, e.g. Name 1.
Question 1 - What formula I can used to make sure the SUMIF function refers to that specific value, e.g Name 1?
Question 2 - Is my SUMIF function correct to calculate the SUM of hours to a Task? This is due to the data table setting? find attached:
View 3 Replies
View Related
May 12, 2009
I'm trying create a Sum(If formula with 2 conditions, but whatever I try doesn't seem to want to cooperate. I want to add up the sum of all invoices according to the country they deal with and the month that they were created in. I've attached a sample file showing what I'm trying to accomplish. Here is how far I've gotten so far: =SUM(IF(Main!$L:$L;B21*Main!$Z:$Z;C20);Main!$H:$H)
I've tried other forms as well but even though this only returns #Value!, at least the formula is accepted.
View 5 Replies
View Related
Jun 14, 2009
I have an old version of Excel and want to use sumifs()
This means I want to add data based on 2 conditions. What should I do?
View 9 Replies
View Related
Jun 15, 2009
Im using a formula to identify a job description then add up the quantity which works fine. =SUMIF(Master!A1:A10,"Bread",Master!C1:C10)
But my problem is, I have a second description in column 'B' for example called 'White'. I've been trying to get the formula to look at column A & B and if they match criteria in formula, then add up C. But can't seem to get it to work with the second criteria.
View 2 Replies
View Related
Aug 31, 2009
How can I do this?
F9= 322,539.77
D4=263,800
E4=.01
F4=1319.00
I want to say,
if F9 falls between 0 and 137099 dollars, the answer is D2.
If F9 falls between 137,100 and 263,799 then take F9-D3*E3+F3.
If F9 falls between 263,800 and 527,599, then take F9-D4*E4+F4
I tried this, but obviously it didn't work:
=SUM(IF(F9<=137099,52),(IF(F9<=263799,F9-D3*E3+F3),(IF(F9<=527599,F9-D4*E4+F4))))
So if calculated correctly, the answer should come up to 1906.40
View 9 Replies
View Related
Sep 22, 2009
I have a workbook containing 2 tabs.
Tab 1 has columnar data with column A containing locations; however, each location may be listed multiple times on different lines - once for each piece of equipment at that location. Tab 1, column B has a piece of equipment listed. Tab 1, column C has a price associated with the piece of equipment in column B.
Tab 2 contains contains columnar data with column A containing locations. I need to populate Tab 2, column B with the sum of all equipment associated with the location in column A.
I have used VLOOKUP to try and find matches for the lookup_value in Tab 2, column A, then return the value of the price from Tab 1, column C.
Problems have been that it returns only the first instance of the location data, not a sum of all. I know the formula is missing something, but cannot ID it.
Formula as used:
=VLOOKUP(B4,'Tab 1'!$I$4:$AI$461,3)
B4 is the column on Tab B that contains all of the locations (lookup_table)
Tab 1 I4 thru AI461 is the table_array where all locations are listed
3 is the col_index_num indicating that the price is in the 3rd column over
View 14 Replies
View Related
Jan 21, 2010
I'm using sumif to get the total number of OT minutes/hours rendered by an employee. In a company with 1K employees, doing so proves to be tedious as I need to rename the criteria for each formula. Is there an easier way to do this? See attachment for more details. I'm using Excel 2003.
View 2 Replies
View Related
