# Find Smallest Number With Multiple Criteria

Sep 2, 2008
I need a formula that will give me the smallest number based on multiple criteria.

For example, I want to find all:

1. Product A

2. 1 Liter

3. Smallest Price

In other words, out of all 1 Liter Product A's, I want to see the smallest price point.

I tried a sumproduct but I can't get it to work. Something like =sumproduct((A1:A100="Product A")*(B1:B100="1L")*(small(C1:C100,1)))

How can I get the small formula working within the sumproduct? Or is there another workaround?

View 9 Replies
ADVERTISEMENT
Mar 10, 2014

I'm looking to find the smallest number in a column that fits a certain criteria, and I'm not sure where to put the if function, or if I can use some other 'qualifiers'.

I was thinking something along the lines of If(E:E<>2, Small(E:E, ROW()-1),____) but I'm not sure what the "false" argument for the if function would be in the blanks. I don't want there to be one.

Where E:E is the range I'm looking in, and 'not equal to 2' is the criteria I'm looking to have filled.

So, "Find the smallest number in E:E that does not equal 2" would be the wording.

View 5 Replies
View Related
Jan 25, 2014

I want to find the largest and smallest value for a number... E.g.

68734 the largest value is 87643 and the smallest value is 34678

39823 the largest value is 98332 and the smallest value is 23389

43089 the largest value is 98430 and the smallest value is 3489

View 4 Replies
View Related
Aug 23, 2007

I have a workbook with 4 sheets with Carrier rates based upon weight breaks and cities. I.E. Carrier 1 services City 1 @ 1.30 per LB for 0 -100LBS and 1.55 for 101 - 199LBS. What I need is a forumla so that someone can input the city and weight on sheet 1 and it will search the 4 sheets and return the cheapest Carrier in the event that there is two carriers with the same rate it would return both. It would also need to return the rate for that city and weight.

For example

User inputs "City 1" "112 LBS" formula searches the 4 carrier sheets and returns values "Carrier 1" & "1.55" as this is the cheapest carrier and the rate they charge per LB for this city.

View 9 Replies
View Related
Mar 10, 2009

I've a large excel file contain a sheet of values. With a Macro I would like to

1) find the smallest absolute value in a certain column

2) find the row number where this value was found

3) Copy certain values from this row to another place.

View 4 Replies
View Related
Mar 26, 2009

I am trying to write code that will locate either a 0 or the smallest negative number in a range, eg,

example 1,

8, 6, 2, 0, -0.5, -2

the result would be 0

example 2,

8, 6, 2, -0.5, -2

the result would be -0.5

View 6 Replies
View Related
Mar 8, 2007

I am trying to find the smallest values in a list. that part is easy (use SMALL). But, I want the ref to the cell where that value is in, not the value itself. Additional problem occurs when more than one of these values are the same. Example: A1 = 1; A2 = 3 ; A3 = 2 ; A4 = 2 ; etc. If I want the smallest, I want "A1" as result, for the 2nd smallest I want "A3", for the 3rd smallest I want "A4". So you see that a MATCH formula will not work beceause some of the values are the same.

View 2 Replies
View Related
Dec 21, 2006

How can i easy return the smallest number higer than 0 from a set of numbers?

View 11 Replies
View Related
Jan 25, 2013

I need a formula that will find the second smallest number in a row. The row can contain as many as 200 numbers with many of them being zeros. That makes using the "small" function impossible.

View 9 Replies
View Related
Aug 13, 2008

I am having some data running into 60 (C:BJ) columns and 200 rows. It is the record of dates query raised and answered with refernce to query number in the A coulmn. I have zero value as entry for the unused cells in this range due to some constraints. I need to find the smallest and the largest non-zero date in this range for analysis purpose. I plan to use the difference between these 2 dates to calculate turn around time for a query.

Problem is to find the smallest and the largest non zero date within range C6:BJ6 which would be used as input in the Networkdays formula.

View 9 Replies
View Related
Jul 17, 2012

I want to count the smallest numbers in a range of 10 cells after omitting the 4 largest. I have been using the formula

=SUM(IF(ISNUMBER(LARGE(E5:N5,{1,2,3,4})),LARGE(E5:N5,{1,2,3,4})),D5),

This works perfectly in that in the desired cell it enters the 4 largest numbers and a specified cell. I then want to be able to total the remainder.

View 9 Replies
View Related
Dec 29, 2009

Currently we are using the formula below to get an average of column E in a spreadsheet. I was wondering how to modify the formula to get the smallest number in that column rather than the average.

=IF(ISERROR(SUMIFS(E:E,A:A,K2,B:B,K3,C:C,K4,D:D,K5)),"Not Available",AVERAGEIFS(E:E,A:A,K2,B:B,K3,C:C,K4,D:D,K5))

View 9 Replies
View Related
Mar 30, 2007

Is there a forumula that will allow me to find the smallest number in A1:A10 that is also larger than the number in B1?

View 14 Replies
View Related
Apr 14, 2009

Is there any formula witch i can use to find the smallest prime factor of a number?

Example: In A1 i write 30 (30=2*3*5 (the prime factors))

in B1 i write a formula, and the result will be 2 (smallest prime factor)

What is the formula?

View 7 Replies
View Related
Mar 21, 2012

very simple and short code.

Code:

Dim selection As Range

Dim minimum As Integer

Sub Sequence()

Set selection = Range(Cells(4, 3), Cells(6, 5))

minimum = Application.WorksheetFunction.Min(selection)

' and now I need an address of that smallest number for this:

' IF (column number + row number) > value of that smallest number

' do

End Sub

It's not exactly the program I need, but it's simple.

View 9 Replies
View Related
Feb 11, 2010

I'm using this =SMALL(A:A,1) in a cell but i need it to ignore the number 0.

Because where theres no data it shows 0, but i need it to pick the lowest from the places where that data actually shows. something like this

0

0

54.2

0

82.6

0

0

55

So i need t get that 54.2 out of there.

View 9 Replies
View Related
Nov 6, 2009

I have 3 columns:

A has a sequential number

B has a date

C has a date

I want to find the first, second, and third occurrance when the following comes up in my list: B=date, C=blank.

View 2 Replies
View Related
Jul 20, 2012

Find Function with Multiple criterias. Ex-

ADC XE AD ADKO R Ad Know Op

ADC XE AD ADKO X Ad Know

ADC XE AD ADKOAd Know

KNE XE KN ACDA

If this is my data in Column A, I need to find the names which contains " R " and " X " in 16th digit. I can do it using find formula twice but is there a way to do it in on shot..?

The formula I'm using is =FIND(" R ",A1,16) it gives me numbers like 16, 17, 19, 20 etc but I'm interested only in 16 digit so I just filter for 16 and copy the data to seperate tab.

View 9 Replies
View Related
Sep 18, 2006

I am using 2 criteria on one spreadsheet to identify an get a value on another spreadhsheet.

The criteria are Sex (Male or Female) cell C8 and Age (ranging from 0 -100) cell D8.

I want the formual to Look Up the Life Expectency (from the other worksheet) The worksheet has colums that contain age, sex and life expectency.

I can only get VLOOKUP to work with one criteria;

=VLOOKUP(D8,"Mortality Table"!$B$10:$E$109,5,False)

How can I find the Life Expectency utilizing 2 criterial, age and sex?

View 9 Replies
View Related
Nov 27, 2013

I have an excel sheet with multiple columns. In one column I have X's and in another I have a limit. I need to look down the stuff column, find every X, match each X to its corresponding limit and then compare all the limits and return only the smallest limit found. I.e if I was looking down the stuff column I would find 4,6,8,8,3 and thus I would return 3 as my value. I am very new to working with excel formulas,

limit

something

stuff

4

x

[Code]....

View 4 Replies
View Related
Dec 24, 2012

I want to know the macro code to find out the oldest date in the Calculation sheet of the attachment based on two criteria.

The criteria should be if the Item ID on Calculation sheet matches the Item Id Pivot sheet and then if the Status on pivot sheet and calculation sheet matches then the macro should find out the oldest date for each item ID. And once the oldest date is identified the difference on pivot sheet should be added to the cost basis column on calculation sheet to the already found oldest date lot.

Find the attached excel sheet : Book1.xls

View 4 Replies
View Related
Oct 6, 2009

I have a few calculations I cannot solve for life of me. I know you guys can figure this out. In the example attached you will see the list of the numbers that were drawn (More numbers will be added as time continues).

I was able to use " countif" to find the total number of time each number was drawn.

My delima, I need:

1. Count the number of times each number was drawn by person.

John starting in cell J7

Mary starting in cell J11

2. I need to type in 4 digits in cell J19, and find all possible combinations from the list of numbers drawn

List what combination of numbers that was drawn including the date it was drawn by person starting at cell I20 using the format as shown

View 9 Replies
View Related
Jun 7, 2009

I have the following dataset:

[Date] [Category] [Currency] [ExchangeRate] [.....], etc.

1-3-09 A USD 0,8

1-6-09 A EUR 1

1-7-09 A USD 0,7

1-8-09 B USD 0,9

1-9-09 B USD <formula>

I'd like to have the value of <formula> looked up in older records. Currency and category should match and it should pick the exchange rate with the maximum date.

Which formula and what syntax should I use to have this done?

I use Excel 2007.

View 10 Replies
View Related
Sep 14, 2006

I can't seem to find the answer to this. In VBA, how do you Find a cell or row based on multiple criteria? All I need is to find it's location. If it helps, here's an example:

Sheet 1 contains source data:

Date Name Time Complete

Sheet 2 contains an interactive worksheet using this data. On Sheet 1 there's an entry: 9/12/2006 George 03:30 PM

Sheet 2 displays those records with adjacent cells for entering data. When data is entered, I need the system to go back to Sheet 1, find the entry that matches this one, and put an "x" in the Complete column.

View 6 Replies
View Related
Dec 8, 2006

I have a sheet with "Yes", "No', and "N/A' in most of the Columns. (E - Q) and about 100 rows of data.

I only want to count the number of rows that have "No" in column E and Yes in all the others. If "no" occurs in any of the other columns in that row (D-Q) I dont want it to be part of the count.

View 9 Replies
View Related
Jun 28, 2014

I'm working with many rows of data (500,000+) and many columns. To simplify my question, I'm going to provide a simple example using made up numbers and only the columns I'm concerned with.

BillT Doc.ItemQty

F11231012.00

F2123205.00

S1123105.00

RE321202.00

F2321108.00

F2321201.00

RE321203.00

RE999808.00

F27771001.00

RE7771001.00

I am trying to remove the docs that have two Bill types that cancel each other out, where the qtys match and highlight the rows where the qtys don't match. The macro needs to have the positive and negative bill types programatically entered, where for example F1 and F2 are positive and S1 and RE are negative. Keep in mind the data may not necessarily be in order as it is above.

So for example with data above, the rows for doc 777 would be removed completely because the item numbers are the same, the qty is the same, and the bill types oppose each other. Doc 123 and Item 10 lines should be highlighted since their bill types are opposed bu their qtys don't match.

I hope this makes sense. I tired to achieve this using multiple loops and arrays, but ran out of memory when working with the entire set of data. I'm assuming their must be a better way to do this, I'm hoping some of the intelligent individuals here will be able to point in the right direction.

View 8 Replies
View Related
Nov 3, 2009

I have attached a spreadsheet with a small indicative data set to assist in understanding. I am trying to count the number of documents each individual has assigned to them that are not yet 'completed' (ie REGISTERED, IN WORK, REVIEWED). The problem I am trying to overcome is that the document state can be 1 of several values indicated in the same column.

I have tried using this SUMPRODUCT formula:

=SUMPRODUCT((($E$2:$E$11="REGISTERED")+($E$2:$E$11="IN WORK")+($E$2:$E$11="REVIEWED")*($B$2:$B$11="Jones")))

but it is generating incorrect values!

Specifically:

- Jones shoulld return 1

- Franks should return 3

- Smith shoudl return 0

View 4 Replies
View Related
Jul 16, 2012

I'm trying to figure out how to write a VBA Match function that can look for multiple criteria and return the row number of a successful match.

I have about 255,000 rows of data on the worksheet "Filtered". Column B contains my Item Number and Column D contains the supply source. I want to find the row where ItemNumber and SupplySource match my variables and then return the value from Column C.

I can do a match for one criteria, but where I'm having problems is getting it so the two matches are on the same row.

View 4 Replies
View Related
Apr 9, 2014

i am looking some formula to make sequence number with criteria;

1) if in cell B2 contains only single/one data ----the result is mark "-"

2) if in cell B2 contains several data (not single)----the result is auto numbering with adding mark "." (dot) and start from 1.,2.,3.,4,etc........

for the detail,

condition 1 (multiple data in col.B)

number

name

1.

john

2.

mike

3.

sisca

etc...

condition 2 (single data in col.B)

number

name

View 2 Replies
View Related
Jan 14, 2010

I am using the following array equation to return the value in column 1 for the smallest 5 numbers. It works for small 1 and 3 but i get a #NUM! for 2 , 4 , and 5. The smallest 5 numbers are:

0

3.196408441

4.491972091

4.491972091

4.491972091

Equation:

=INDIRECT(ADDRESS(SMALL(IF((NumRange)=SMALL(NumRange,1),ROW(NumRange),""),1),1))

View 12 Replies
View Related
Sep 22, 2006

I have 6 columns of data laid out as follows,

columnA columnB columnC columnD columnE columnF

XXXXXXXX XXXX XXXXXX XXXX XXXX XXXX XXXX XX

XXXXXXXX XXXX XXXXXX XXXX XXXX XXXX XXXX XX

I need to find data in columns B and C and add the figures from column F, which drop down one line. I have written the following formula which is giving me far higher figures than I should be getting

= SUMIF($B$2614:$B$2640,"bhree*",IF($C$2614:$C$2640,">=01/07/2006",( OFFSET($C$2614:$C$2640,1,3,1,1))))

View 9 Replies
View Related
Feb 5, 2008

I have attached a small copy of the worksheet.

I need to count when the following set of 3 criteria criteria are met:

1: The Assessor Initials = DS

2: the month = Mar

3: Check 4 Ranges = columns I, K, M, O

I've tried using sumproduct (sucessful for other data) but not able to get it to work with dates.

View 9 Replies
View Related
Jun 7, 2013

I have huge sorting job in Excel.I have a excel file from each year.And I put these together to a huge excel file. And I need to track down the last activity if the customer is listed multiple times.I could just go through and delete the rows, but the last row.But, that takes a lot of time.

Format:

Last activity (year only) - Customer number - Customer name

2010 - 1001 - Company A

2011 - 1001 - Company A

2012 - 1001 - Company A

View 3 Replies
View Related
Oct 10, 2009

Sheet A – is of the following format

SlNO, Item_Description, …,…

The item_description is a combination of XXX, Part_no and Part_description all clubbed randomly. ( So I cannot use text-to-columns to bifurcate them).

Now Sheet B contains the part_number as follows :

SlNos, Part_no, ….,….,

I need a macro ( a procedure / function ) in Excel-vba, which would select each part_no in sheet – B one by one, find all those rows in which it appears in Item_Description column of Sheet-A and add a comment in the next column to it.

The Vlookup function cannot be used as it is not an exact match as the part_no is embedded among other things in item_description.

View 5 Replies
View Related
Feb 21, 2014

I have come up with 5 countif formulas that work perfectly separately but need them to be combines into one big criteria.

=countif(PM[ProjectAge],">=200"

=countif(PM[Customer Name],"*" & purch & "*"

AND so on for each criteria. H

View 7 Replies
View Related
Jan 23, 2009

I am trying to count the number of rows that have values greater than 10/01/2008 in either of two fields. I tried following formula but instead of giving total number of rows, it returns a random date.

View 2 Replies
View Related
Dec 18, 2006

My spreadsheet has 6 columns and 10 rows containing various numbers. Using "FIND" I can easily locate any given number in any column or row. Problem is rather than trying to find just one number at a time I would like to find 6 specific numbers [or any of the 6 specified numbers] in any row at once. I've tried entering the 6 numbers but it will not allow the search. I've tried separating the numbers using commas, colons, semi-colons, etc., but to no avail.

Is there a way I can enter 6 specific numbers at once in the "FIND" which will allow "FIND" to find my numbers in any row?

View 9 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
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
May 18, 2014

Macro for AverageIFS, with multiple criteria in the same criteria range....

View 9 Replies
View Related