Tracker
HOME    TRACKER    Excel

# Find Smallest Number With Multiple Criteria

## 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?

Related Forum Messages:
Multiple Criteria Lookup For Smallest Value
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.

Find 0 Or Smallest Negative Number From Range
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

Find Smallest Absolute Value And Row Number With A Macro
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.

Find Smallest In A List
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.

Smallest Number Which Is Not Zero
How can i easy return the smallest number higer than 0 from a set of numbers?

Smallest Number In The Column
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))

Find Smallest And Largest Non Zero Date In A Range
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.

Smallest Prime Factor Of A Number
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?

Smallest Number In A List/Bigger Than X
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?

Smallest Number From List But Greater Than 0
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.

Using Multiple Criteria, Find Value
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.

Multiple Criteria To Find A Value On A Worksheet
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?

Count With Multiple Criteria And Find Permutations
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

Counting Number Of Rows With Multiple Criteria
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.

Find Value Matching Multiple Text Criteria And Max Date
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.

Macro Code: Find Cell Or Row Multiple Criteria
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.

SUMPRODUCT With Multiple Criteria: Count The Number Of Documents
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

Count Number Of Instances Of Dates By Month With Multiple Criteria
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.

Sum With Multiple Criteria A Number Of Rows And Columns From Original Data
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))))

Macro To Find A Number In Multiple Rows
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.

Multiple Criteria And SUMPRODUCT (count The Number Of Rows That Have Values Greater Than 10/01/2008 In Either Of Two Fields)
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.

Address Of Smallest 5 Numbers (return The Value In Column 1 For The Smallest 5 Numbers)
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:

Lookup Multiple Criteria & Sum Result On Multiple Criteria
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.

MULTIPLE CRITERIA USING "FIND"
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?

Multiple Criteria Count Between 2 Dates & Other Criteria
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!

Copy Rows Meeting Multiple Criteria On Multiple Columns
1. Copy data from original file (I do not want to do anything in the original file) into the spreadsheet (Target worksheet)where the code should run.
2. In sheet 1 of Target Worksheet, there are 2 columns which I need to set criterias on namely Column D and Column L
3. In Column D, I want to specify 3 criterias namely A, B and C
4. In Column L, I want to specify 5 criterias namely London, Frankfurt, New York, Sydney and Tokyo
5. If Criterias in 3 and 4 are met, copy all rows into Sheet 2 of Target Worksheet

Count Rows Meeting Multiple Criteria Of Multiple Values
I am trying to find a formula to count rows that meet multiple criteria, but one of the criteria can be multiple values. I have a list of people with a list of clients that they are responsible for. Each person is responsible for 10-20 clients. Every day I run a report that shows the project worksheets submitted for each client and if money has been awarded or not.

I'm wondering if there is a way to count, for each person, the number of project worksheets that show "awarded" in column K. That would mean that I would have to look for, for each person, any of their multiple clients in column B and "awarded" in column K.

I am trying to put the formula in D2:D9, as I use A2:D9 for a chart. O1:P79 contain the names of the people and the applicants that they are responsible for. A17:D158 contains the list of project worksheets (updated daily). I used =SUMPRODUCT(COUNTIF(B17:B999,P1:P14)) to count the actual number of project worksheets for each person, but I can't figure out a way to modify that to add in the "awarded" criteria also.

Search Multiple Values In A Cell With Multiple Criteria
I am trying to search for multiple values in a cell with Multiple criteria.

E.g: Please find attached the sample excel data.

I tried using VLOOKUP which is not working as I am having multiple values in column A.

Find Cell Value Row Number & Use For Column Number
to update these values via a form in this sheet. I can find the correct row to be edited by entering a value from column A and B. The problem is if I want display the values of that row first and then change it. If I want to change row 10 data how can I bring back the value in ROW 3 AND THE COLUMN VALUE? The next step would be to do the actual update if I want to change ROW 10 to "Ooi" and a sales value of 200?

This is what I have done so far:

Dim myRows As Integer

With Sheets("Mrt")

'Retrieve history information for row
For myRows = 4 To 49
If comboxDay.Text = Range("A" & myRows).Value And textboxdescription.Text = Range("B" & myRows).Value Then
textboxbedrag.Text = Range("C" & myRows).Value
chkBTW_Ja.Value = Range("D" & myRows).Value
txtNota.Text = Range("S" & myRows).Value
End If
Next
End With

Picture attached to show how sheet looks like.

How To Satisfy Multiple Conditions Using Multiple Criteria
I'm trying to divide my data into 6 different groups, based on 2 different criteria. First, I am not sure how to write the logical test to take 2 columns of data into consideration (using "&" and "AND" do not work; I am not sure what else to try), and second I can't figure out how to write the formula so that it can select from 1 of 6 conditions.

So, overall here is what I want:

If DL2=3 and CK2=1, then I want this to be labeled as '1'
If DL2=3 and CK2=2, 2
If DL2=1 and CK2=2, 3
If DL2=2 and CK2=2, 4
If DL2=1 and CK2=1, 5
If DL2=2 and CK2=1, 6

These 6 conditions cover all possible combinations of numbers in the two columns.

Sum Across Multiple Tabs, Multiple Criteria
Excel 2007

My workbook contains 13 tabs - 1,2,3,...12, and Summary

My data starts on line 4 of every sheet but varies in length - so far the longest goes to line 30.

Rows used on all 13 sheet are as follows:
A - contains facility names
B - contains a two or three letter code
C - contains hours
D - contains dollars

On the Summary tab I have listed all the facilites and two or three letter codes. I need to sum column "C" on tabs 1-12 when they match columns A & B on the summary tab. I have tried the following but can't get them to work:

=IF(\$A5=""," ",SUMPRODUCT(--('1:[12]12'!A\$4:\$A\$50=\$A5),--('1:[12]12'!B\$4:\$B\$50=\$B5),'1:12'!D\$4:\$D\$50))
I did not put the [12] excel added that automatically I had 1:12

=SUMPRODUCT(--(THREED('1:12'!\$A\$4:\$A\$50)=A10)*(THREED('1:12'!\$B\$4:\$B\$50)=B10),(THREED('1:12'!C4:C50)))
I just seen the THREED for the first time today and am not sure if this was the correct place to try but it didn't work anyway

Sum Multiple Criteria On Multiple Worksheets
I need a summary page in a workbook that will pull data from multiple workseets with mulriple criteria.

I have treid sumproduct, but that doesn't seem to be working when I select a range from multiple workseets.

Deleting Multiple Rows With Multiple Criteria
I have a need to delete multiple rows with multiple criteria; criteria contains about 400 different and unique entries that have to be deleted. The code I obtained from this forum was modified but only deletes some entries and bypasses others. Row 1 is header and column 1 contains criteria that it it meets the " Case" it is supposed to be deleted. Example of code is below the Case has been shorted, for posting but normally each case runs the maximum length allowed. Please let me know where I messing up, I am thinking the it needs to be looping until all entries are deleted but I don't know how.

Sub DeleteRecords()
Dim n As Long, lastrow As Long
Dim ty As String
Dim a As Integer
lastrow = Range("D65536").End(xlUp).Row
a = 1 'this is the column in which you will find the notation finance numbers (Col X = 24)
For n = lastrow To 2 Step -1
ty = UCase(Cells(n, a))
Select Case ty..........................

Multiple Criteria - Multiple Results
I need to look up a value from a worksheet using multiple criteria, and return MULTIPLE results.

I have a table with comments written by different people about specific items. I would like to be able to type in that item name and return all those comments. BUT - it's not JUST the item name, but also on a specific date or some other criterion.

Currently I have managed to do this successfully with a single criterion, as in this array formula:

=IF(ISERR(INDEX(F\$1:F\$1001,SMALL(IF(A\$1:A\$1001="ABC",ROW(F\$1:F\$1001)),ROW(1:1)),1)),"-",INDEX(F\$1:F\$1001,SMALL(IF(A\$1:A\$1001="ABC",ROW(F\$1:F\$1001)),ROW(1:1)),1))

I then copy that formula to a few more rows and I indeed get a list of different names (F1:F1001) which match my criterion "ABC". But - I have at least one more, or even two more criteria. I've tried a whole lot of different things and I just get errors.

Has anyone tried something like this? If you have - how should I modify the above formula to allow for additional criteria? For example, not only A1:A1001 equal to "ABC," but also B1:B1001 equal to "XYZ", and so on.

Find Multiple Instances In Multiple Workbooks
I'm trying to search through a group of Excel files for specific values in column B and then copy and paste the value from column C of the same row into the current worksheet if the search term is found. Thanks to some help from turtle44, I can do that if the search term only appears once, but if it appears more than once, I can only find the first occurance.

I have searched through te archives and found lots of people asking for the same thing. The common suggestion is to use .FindNext, but no matter how I try to use FindNext, it doesn't give me the results I want. It either seems to find the first occurance again or to find nothing at all.

Here is a simplified version of my code, if someone could just explain how to find the second occurance of "Use Code" and past the relevant value into column F of the destination workbook, I'd appreciate it and I should be able to work out the rest on my own.

Sub SearchAllXLSFiles()

Dim lCount As Long
Dim wbSource As Workbook
Dim wbDestiny As Workbook

On Error Resume Next

Set wbDestiny = ThisWorkbook

Smallest Value In Range Linked To Another Value.
D3 = SMALL(H6:H45, COUNTIF(H6:H45,0) +1)

to determine smallest value in range excluding 0 is it possible to link that smallest value to another value like a name?

Eg. H6 = 5 K6 = Ben
H7 = 4 K7 = John
H8 = 9 K8 = Ted
Thefore D3 = John

The Smallest Value That Is Greater Than Or Equal To
I am looking for a function like MATCH if the match type were set to -1. However my data is sorted in ascending order. I am mining data from a Pivot Table, and it has dates across the top. Of course the pivot table will have the data sorted in ascending order from left to right. I want to find the first date that is greater than today. With weekends and holidays I can't just use TODAY()+1. Is there a function that can do what I am asking? Also I do not want to change the pivot table itself.

Finding Non Zero Smallest Numbers
Find smallest numbers in the range ignoring zeros.
I want it returned as value "1", all others - as value "0".
Example: range A1:J1 contains - 5,0,7,3,6,5,8,0,9,3
desirable result in the range A2:J2 - 0,0,0,1,0,0,0,0,0,1

Largest And Smallest Sequences
I am using excel 2007 and I need help with the following if anyone would be kind enough. I have a dynamic array that consists of stream of 1's and 0's. I want to be able to find the largest sequence of 1's and the smallest sequence of 1's. So at a point in time the array mite be 111110001101111000 and I want to be able to dertermine the longest contiguous sequence of 1's. But as I say the array is dynamic and so I need to do that after each input.

LOOKUP Return Smallest Value
I have a large set of numerical data, with each column having a text heading in the first row.

I want to use a function similar to HLOOKUP that can return the numerical value in a certain row of a column that has a specified heading....AND if there is more than one numerical value that meets the criteria, I want it to return the lowest numerical value.

My numerical data cannot be sorted into ascending order.

Finding Smallest Value In A Column
In column A, I have a list of names.
In column B, I have a list of values.
I want column C to show me which name has the smallest value from column B.
In other words, if there are 5 names, I want ONE name to give me a value of 10 (for the smallest value), and the rest of the names I want to show zero.

I also have in column D another list of values.
In column E, I want to show first, second and third place amongst the list of values from column D. The rest of the names I want to show zero.

Return 3 Lowest/Smallest
I found this spreadsheet on ozgrid, but can not locate the original post to move my question to -- mods please move if you can.

As for the question:
The spreadsheet attached searches an array, and shows the top 3 scoring names. How do I modify it to show 3 lowest scoring names without changing the rank field?

Returning Second Smallest Unique Value
I'm creating a spreadsheet and have had difficultly creating a function that returns the second smallest unique value.

For example if my data set is:

2, 2, 3, 3, 4, 5, 6

I'd want the function to return "3". I've tried playing around with the SMALL(array, k) function and trying to use a COUNTIF function for "k".

Return Smallest Value, Larger Than Specified Value
I know function Vlookup finds the largest value less than or equal to the given value. However, If I want to finds the smallest value larger than or equal to the given value, How can I do it? assuming I do not want to use VBA.

Using InStr To Find Number Within A Number?
I work for a store and we're migrating to a new Point of Sale system. The new POS uses the entire UPC code while our existing one uses only partial UPCs, so I have to update from the partial to the full. ex. Partial 3378093 - Full 0033780935

The partial UPC will always be included in the full UPC, the difference is the dropped leading zeros (and possibly other numbers) and the check digit number at the end. What I have is a spreadsheet with with two worksheets (Old, New) - one has our old UPCs and other information, the other has the new, full UPCs and more information we'll need. What I want to do is write a function that will search through the full list of new UPCs to find the one with the old UPC inside it and return the full UPC. InStr seems the right function to do this - correct?

The part I'm having trouble with is writing something that will search through the full column of new UPCs using InStr. I've searched through the forum for a similar situation but the few I've found aren't working or appropriate. I don't want to include any code I've written because 1) it doesn't work and 2) it would be way too embarassing!

Averaging Smallest, Non-zero Numbers In A Row
I'm struggling to get the syntax right for this formula. I've been trying variations on the following formula but can't get it right:

=AVERAGE(SMALL(IF(AP8:AZ80,AP8:AZ8),1)))

Columns AP:AZ:

1.8921.7621.6501.1741.2261.2991.3020.0000.0001.1461.333
Any column in this range could have a zero value.

What I need: The average of the 8 lowest non-zero numbers in range AP:AZ

Lookup Nth Largest/Smallest Value
Is there a way to reverse a lookup function?

I have a s/s attached, basically B is like an output of figures after some calculations. And I am trying to find from largest to smallest values and then having column E reflect the Letter beside that number...

tried using match but messed up.

Return Nth Greatest/Smallest Value
I want to ask if there is a way to find the next greatest maximum without a ton of auxiliary coding....Is there a function which does that in Excel? For example from the following list it would give 45:

1
34
45
26
78
29
7

How Can Find A Value Using Two Different Matching Criteria
Col cCol dFormula
Lease TermCompany #6023%
3637%
4843%
6052%
12620%
247FALSE
368FALSE
48105%
609FALSE

Residual Table

Company #
127
438
569
10

Col b
Lease TermResidual %
1210%20%9%
245%9%6%
364%7%3%
483%5%2%
602%3%1% ......

Find Average With Criteria
In column A is the staff name, column B is the start date, column C is the end date and column D is the difference.

I need to find the average number days for task completed for each staff for the week. I need a formula that will look for a particular staff name in column A ( which appears many time randomly in column A for each day of the week) and calculate the average days for the total task completed for the week.