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.
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.
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.
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.
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.
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.
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
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.
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
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.
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 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.
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.
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.
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.
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.
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.
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
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.
