Sum Quantites Against Given Criteria
Jun 12, 2009
here it a very simple sheet for me to explain what im trying to do
15
26
12
24
23
13
31
1 =10
2 =13
3 =1
ok i can manual add all the values in each box beside 1 and the same for 2 and the same for 3 but i'd like to write a formula that does this automatically within a range as the sheet is very large. so i need a formula that checks column A for 1 and for each time it sees that it adds the corresponding value beside it and the same for 2 and 3.
View 2 Replies
ADVERTISEMENT
Feb 24, 2010
Most of formulas I thoght of are only counting number of times it appears or comes back with the number of the unique records under one product.
I have two columns
In the first one I have product ID Numbers
In the second column I have Product quantities ....
View 6 Replies
View Related
Dec 6, 2013
I have a table with 3 columns with an unkown number of rows (text, date, date) that is being imported daily.
I want to create a 4th column with dates starting from today and each subsequent row be one day earlier. I want to look at 30 previous days.
I then want to count the number of rows (looking at column 1-3) with the following criteria:
Countif column2<= "date in colum4" AND column3< "date in column4"
View 6 Replies
View Related
Sep 22, 2009
Here is what I have. 4 Worksheets. The first worksheet is a summary page. I have 350 personnel that are broken down into three different groups. So each group has it's own sheet. Here is what I need to accomplish. Results need to be posted on the summary sheet.
I need to compare cells B2 & D3 for each row on a worksheet and display the number of times they match on a worksheet. For example how many times does EP & EP match on a certain row. I need to compare cells B2 & D3 for each row on a worksheeet and display the number of times they don't match on a worksheet. For example how many times does EP & MP occur. I've attached an example for reference
View 5 Replies
View Related
Apr 1, 2014
Is there a way to provide filter with a list of criteria but when it doesnt match all of the criteria it still uses the filter on the criteria that it does match?
E.g i have this code
ActiveSheet.Range("$A$7:$N$31997").AutoFilter Field:=1, Criteria1:=Array( _
"A", "B", "D", "E", "H", "I", "R"), Operator:=xlFilterValues
However sometimes for example B will be missing, or H or B H I will be missing etc... is there a way to provide all of the criteria and it will not error if the criteria is not all there?
View 1 Replies
View Related
Mar 24, 2014
I've attached a sample workbook to show what I am trying to do. I would like the formula to say "if Sheet2!A:A is "MON" and if Sheet2!R:R matches Sheet3!A:A, and if Sheet2!I:I doesn't match any of the values from Sheet1L:L, then I'd like the sum of Sheet2!F:F. It seems pretty simple but I've tried a million different variations of SUMIFS, SUMIF, IF, AND, etc. and I can't figure it out.
View 1 Replies
View Related
Oct 22, 2008
I am trying to make an "intelligent" auto filter that with filter with increasing restriction until a certain criteria is met.
The list runs from A5:G20. In coloumn G is the number of hours associated with each event. And in A1 I have the percentage of items showing/whole list so it I have 15 rows on the list, and I filter so that only 5 are showing, cell a1=33%
How can I make a macro that will autofilter until the the a1=5%
Like having filter criter = equal or greater then 1 hour,
if a1 > 5%
Then criteria + 1 hour
If A1=<5%, then stop.
basically a seed criteria of 1 hour, adding 1 hour until the value in a1 = 5%
View 6 Replies
View Related
Nov 25, 2013
I am trying to set up a sumif statement with two criteria where if the second criteria is true, the total in the sum range returned is divided by two.
I currently have this:
=SUMIF($G$6:$G$41,"digital",I6:I41)
I want to add in "digital/creative" as a second criteria (from the same criteria range) but I only want 50% of the result of these to be totalled.
is this possible?
View 6 Replies
View Related
May 18, 2014
Macro for AverageIFS, with multiple criteria in the same criteria range....
View 9 Replies
View Related
Apr 5, 2014
What I would like to do is return the total qty of items on the 'QUOTATION' tab that are upholstery and this having been determined by whether there is a Y (for Yes) or N (for No) against the equivalent item in the 'BASIC_LIST' tab.
So in the below example, the total of Upholstered items would be 182 as items 'ST-11' to 'ST-13A - Tray' do not have a 'Y' against the matching item references in the 'BASIC_LIST' tab.
I would like this Y/N column to be the determining factor as opposed to any other criteria or filtering. (Also the Items column in the 'QUOTATION' tab is only ordered in the way it is for ease of view for this query!)
Picture 2.png Picture 3.png
SUM VALUES OF ITEMS THAT HAVE ALREADY MATCHED ANOTHER CRITERIA.xlsx
View 4 Replies
View Related
Aug 25, 2014
I can't seem to get this to work the way I need it to do. Let's say I have a 2 sheet workbook. Sheet1 is called "Order" and Sheet2 is called "005". On sheet2 (005) I have 9 columns that are populated. Row 1 is my header row and then row 2+ is all my figures. On sheet 1 (order) I want A1 to look at sheet2 (005) in F1:F10000 to find all cells that have "N" in the cell. I then want it to return value in column A that corresponds with the "N". I want it to list all the ones from Sheet2 (005).
Sheet 1 (order)
A
052611
052806
052843
Sheet 2 (005)
__A_____B____C_____D_____E____ F
052611_________________________N
154272_________________________Y
125485_________________________Y
052806_________________________N
125478_________________________N
052843_________________________N
how I can write is in a formula?
View 7 Replies
View Related
May 15, 2014
I have a macro I use to filter a large file that I receive each day. Each row of data has an identifier in column C, and this is where the macro looks to determine whether or not to remove that row. Currently, my macro will only remove rows in which column C contains any of the following strings: "B0A0", "B0MH", or "SRB". This part of the macro looks like this:
[Code] ......
I want to add additional functionality to this macro. I will always need rows removed where the identifier in column C contains one of these static strings, but there are times when there or other strings that I would like the macro to remove. Ideally, I'd like to have a range on a worksheet where I can type a string of characters in each cell, and the macro will look to this range when removing rows in my large file. How can I modify this existing code to incorporate this?
Say, for example, that I want to designate the range B2:B50 on Sheet 4 as the range where I want to type these strings.
View 2 Replies
View Related
Jan 29, 2013
I've got a sheet where I've got products in one column.
The second criteria is "week #" for which the row remains consistent but the column varies e.g. "week 8" could be in cell L12 today and in L18 tomorrow.
I would like to return the value of the cell at which a particular product and week intersect. e.g. if "product a" is in B20 and "week 8" is in L18, I want the value of cell "L20" returned.
Product to determine Row #, Week to determine Column # for returning value.
If they interest multiple times, I would like to sum up the values intersecting.
Neither product nor week # is unique in the sheet.
View 5 Replies
View Related
Mar 4, 2008
i m trying to use the sumproduct formula, and OR but i cannot seem to get this right! =Sumproduct(--(A1:A10="Yes"),--(OR(B1:B10="Yes",B1:B10="Mayby")),C1:C10)
I have also tried Array Formula as follows; {=SUM(IF(A1:A10="Yes",IF(OR(B1:B10="Yes",B1:B10="Mayby"),C1:C10)))}
I have also used UDF to for the sumproduct, but cannot make that work! keep giving me value message
Function
Function Customer(Service as Range, Outcome as String, Service2 as Range, Outcome2 as String)
Customer = Sumproduct(--(Service = Outcome),--(Service2 = Outcome2), Result)
-Didnt get thru this bit to start building on the Function! keep giving me #Value!
View 5 Replies
View Related
Jul 30, 2008
I have a spreadsheet which lists letters issued, the date issued and the potcode. I need a formula that counts, on a weekly basis, the number of letters issued to postcodes in Sutton and Bexley.
I have a count sheet set up on one worksheet and the list of postcodes applicable to each area are listed on another worksheet. I have been playing around with sumif, sumproduct etc, but these don't seem to work as I am pointing the formula to a list of postcodes and not an individual postcode. For example, the formula for one week needs to tell me, the number of letters issed to Sutton between 26/07/08 - 01/08/08. This is what I tried -
=SUMPRODUCT(('MU63 NC'!$F$2:$F$10>=COUNT!B$2)*('MU63 NC'!$F$2:$F$10<=COUNT!B$3)*('MU63 NC'!$D$2:$D$10=Sutton!$A$2:$A$50))
But I get #N/A - if I change the last refernce to a specific cell instead of a range it works, but this will make the process very lengthy as there are lots of postcodes!
View 5 Replies
View Related
Dec 12, 2013
I have a list of data that has (Col A) item, (Col B) lot# and (Col C) quantity. In Columns DEF I have the same but column E (Lot#) is blank. Is there a formula I can use that would match Item and Quantity (A and C) with (D and F) and then drop the Lot # form column B into column E if there is a match?
Item
lot
Amt
Items
Lot #s
Yds
10429.50.0
N49950
2.5
0201.019FLAME.0
T37530
9
[code]....
View 5 Replies
View Related
Aug 21, 2013
formula to count two criteria in the one column if there is a third criteria in another column. i.e. if column B = either "A" or "B" and column C="D". I have tried the countifs function without success.
View 4 Replies
View Related
Oct 21, 2006
I am trying to solve a problem. I am currently using this formula
= SUMPRODUCT(--(Sheet2!B2:Sheet2!B300="MARKETING"),--(Sheet2!D2:Sheet2!D300="200612"),Sheet2!E2:Sheet2!E300)
This formula works for me as it is but I would like to add more months to 200612. I want this to also be 200701 and 200702. In another cell there will be up to 10 months. Is there a way to do a Vlookup or something that will look up these months in another table, rather than keep typing them out in the formula?? Otherwise my formula will be very long.
So the info looks like this in excel
MARKETING 200612 -10
MARKETING 200701 -25
MARKETING 200708 -50
ECONOMICS 200709 -30
The info goes on and on. The two variables are the MARKETING column and the month column. My problem is that I would like a seperate table that can be the months. So 200612 and 200701 is one table, and 200708 and 200709 is another table. The table changes often so I dont want to mess with the formulas, rather a table.
View 5 Replies
View Related
Apr 2, 2009
I am trying to find the top ten students house points from each of five different houses.
I have no problem finding the top ten highest points value from each house, but I also need to return the name of each corresponding student in a seperate cell
I have played with some formulas (mix and match method) but the problem is when the same value for house points appears for different students in different houses.
In the attached example (the actual sheet is much bigger) the second return 225, gives the wrong name. I want to have a series of top ten result tables for each house.
Thought I’d cracked it (F6), but now hit a brick wall. Are IF statements the wrong way to go?
View 10 Replies
View Related
Feb 11, 2010
I am trying to solve a problem related to 3 criteria of selling food item. Please see attached file to get my problem and what I am looking for. for example i provided for two days data. I have to do this for seven days. Pls let me know if u hvae any question.
View 14 Replies
View Related
Dec 7, 2005
In workbook A, I have a unique list of customer IDs in column A:
Cust ID
1 000011
2 000012
3 000013
4 000014
5 000015
In workbook B, I have a bunch of product / revenue information related to each customer. Each customer may have multiple rows of data on this workbook, but each row would be a unique Cust & Prod ID combination: ...
View 10 Replies
View Related
Nov 29, 2006
if it is possible to choose several criteria in this formula, for example:
SUMIF('Global Sheet'!F5:F5502,"<"&'Research per date'!E20,'Global Sheet'!K5:K5502)
I would like to add a new criteria after "<"&'Research per date'!E20
View 14 Replies
View Related
Nov 29, 2011
While creating drop down list in data validation, excel 2007 accepts unique NAME only. I have to use same NAME for different criteria
View 6 Replies
View Related
Aug 17, 2013
I want to calculate the sum of a range of cells but when a cell contains a specific figure (or trigger if you like), the sum starts again from the next cell.
So, A1 to A11 can be used for the simple sum but A12 contains data which is not part of the sum and I want A13 to A20 to be a new sum starting from the cell after the trigger was found (A12).
View 8 Replies
View Related
May 31, 2007
I want to know how to do a sum until a certain criteria is met.
January 2
February 4
March 6
April 7
May 9
June 4
etc...
If the criteria is March, I want it to Sum January, Februari and March.
View 9 Replies
View Related
Jun 20, 2008
I have a spreadsheet that I need to set a certian value of time in a cell only if another cell contains a specific criteria.
The value of time may need a formula in itself also, as I am keying off a date minus or plus an amount of days.
View 9 Replies
View Related
May 23, 2009
=IF(OR(J8>9.5,J8=0),M8-P8,-1)
How can I add another criteria to this:
If( J8=0 and M8>0 then M8-1)???
View 9 Replies
View Related
Nov 6, 2006
I am trying to use sum if with multiple sumrange. For example the sum range is from F2:F10, then F12:F20 and then F22:F30.All this need to be build in the same formula to generate the correct value. Is this possible with Sumif or do I need to use another formula?
View 5 Replies
View Related
Jun 7, 2007
I've read all the post about this, but with my little knowledgment of vba, my next question it's impossible to do :$
With the file attached, i want, in the range F45:F53, make a sum of the cells in the range E7:E41 if the row has, in the column A, the same word that appears in A45:A53...
View 9 Replies
View Related
Nov 23, 2007
I have written the following function using Excel 2007 = SUMIFS(S2974_1!$K$30:$K$39, S2974_1!$F$30:$F$39, $B5, S2974_1!$L$30:$L$39,
C$3)
However, i have just found out that the recipients only have access to Excel
2000. When this function is run in the earlier version, I get a # name error
View 2 Replies
View Related