How Many Different Entries For A Certain Criteria

Aug 26, 2008

I have been searching a bit in a lot of the already posted threads but without any luck on my problem.

Normaly in excel you always want to count how many entries for a certain criteria.

Now I just want to know how many different entries for a certain criteria.

Example:

in column A1:A10 I have the value "S" -> group

in column B1:B4 I have the value "40" -> material number
in column B5:B8 I have the value "60" -> material number
in column B9:B10 I have the value "70" -> material number
....
....
etc.

So for the group "S" it is listet with total 3 different "material number" (40,60,70)

So my result should be = 3

how should the formula be on counting for the group "S" how many different values are in a defined area (e.g. B1:B10)

I have tried with sumproduct,

View 9 Replies


ADVERTISEMENT

Retrieve Last X Entries By Criteria

Jul 27, 2007

I have a spreadsheet with columns A:AH. I need to be able to retrieve the last 50 entries based on a number from column M (Area). For example, I need a message box asking me what Area I want. (The choices are 1 to 7). When I select the Area number I need to retrieve columns G:J for the last 50 entries for that Area. In selecting the entries, it needs to ignore any rows where there is either a blank cell or a period in column I. This is a dynamis spreadsheet with rows added almost daily.
Zipped spreadsheet attached.

View 2 Replies View Related

Count Unique Entries With Criteria?

Dec 3, 2012

Count unique entries in a column according to two specified criteria.Specifically, I am trying to count the number of days in a month a supplier has a visited.Below is an example taken from the excel:

COLUMN A COLUMN B
Date Supplier
01-ago-12 Mr X
01-ago-12 Mr X
01-ago-12 Mr X

I want a formula that allows me to see how different dates Mr X has visited in each month. So in August he has visited 8 times BUT only on 5 different dates.

In the case of Mr Y I want the formula to give the answer 5 (even though he has visited 6 times in total, he has visited on only 5 different dates).

In the case of Mr X in September, the answer to the formula would be 2, since has visited on 2 different dates in September.

View 9 Replies View Related

Counting Entries Using Time Criteria

Jun 6, 2006

I need to generate a report that indicates how long it took a vendor to respond, and indicate if the time was less than 2 hours.

I'm having trouble with a formula that will indicate how long it took for the vendor to respond during business hours (8-5 monday through friday). I can easily calculate the total elapsed time, but I'm at a loss on how to account for the after hours time period.

Would this be better handled with VBA?

View 3 Replies View Related

Counting Unique Entries That Meet Certain Criteria

Oct 7, 2009

Every other line is the planned production and the others are filled in after as the actual production
I have a sumproduct formula right now that looks if the line is considered planned production and then if there is a number greater than 0 planned on that day. The idea is to tell me the number of Stations scheduled to run that day.

Here is my problem- if a station is running two different products it gets counted twice.
I am trying to find a way that once a station is counted it only gets counted once even though they are on seperate lines.
Attached is an example of what I have with the formula and below that is what I am looking for

View 2 Replies View Related

Combine Cell Entries Based On Criteria

Mar 12, 2014

I need to first merge cell entries of column 2 if cell entries of column 1 are the same; following columns always retain their corresponding first row (see cells highlighted in yellow in attachment).

View 14 Replies View Related

Populate Listbox That Will Display Only Those Entries That Fit Certain Criteria

Aug 30, 2012

I have a worksheet called 'Letters' with data stretching from A2:W5000. I need a Listbox that will display only those entries that fit the following criteria;

Columns A and O have data in them, Column V does not. The other columns can be disregarded for the purposes of this project. Additionally, is it possible for the Listbox entry to show any formatting that appears on the sheet? What I mean is, I have a conditional formatting set up on the sheet to show any duplicate entries, for example if the entry appears twice the cell fill colour is yellow - I need that to be shown in the Listbox too if it is possible.

View 9 Replies View Related

How To Show All Entries That Meet A Certain Criteria In A Seperate Worksheet

Jan 7, 2010

I have been working with personal record data in multiple workbooks, using sumif, index, vlookup etc to show various required info.

What I now need to do is take all the records of people aged between 16-18 and list this in a new worksheet.

I can get all the records I need in a pivot table but it needs to be something I can add columns to in order to gather further info.

View 7 Replies View Related

Count Number Of Entries Meeting Condition/Criteria

Oct 24, 2006

I have a spreadsheet that has 35000 entries and i am trying to count how many material are assigned to a particular group but cannot get it to work. I have included a test spreadsheet to explain what i am trying to achieve

View 3 Replies View Related

Excel 2013 :: Count Unique Entries In A Table That Match A Criteria?

Jul 11, 2013

2013

Current Year

Q1-13
Q2-13
Q3-13
Q4-13

Totals

Awarded
£19,000.00
£4,000.00
£3,250.00
£0.00
£26,250.00

[code].....

I need to count the number of unique companies that receive money within a specific QTR. I have made this simple example, I have a Table called Awards, with Headings for DATE, QTR, Company, Awarded, on one worksheet, that I need to feed the data into a summary on another worksheet. What formula using table heading can I use to achieve the answer 3 unique companies for Q1-13.

View 5 Replies View Related

Duplicates Finding Entries In Column A Which Match Any Of Column C Entries

May 7, 2014

What I need to do is sort certain entries in longer list (column A, it is in .csv format and needs to be in it so coordinates and names and ID, all sorted with commas) and I have another list (column C) which is shorter list of certain IDs. I googled and tried and got some results for the basic structure but the fuction seems to fail. It doesn't matter how I get that third list done, but there is only one criteria: since the list in column A is really long and those entries need to keep the .csv formatting, the function should copy that info what is in the matching cells.

Let me try to put it simple: .csv cells from column A that have matching ID from column C should be copied to column B (or N).

the function I'm working with right now is

=IF(ISERROR(FIND($C:$C; A1))=TRUE;"0";A1)

View 13 Replies View Related

Identifying Unique Entries Based On Unique Entries In Another Column?

May 29, 2014

I have a list of data and I want to identify the unique entries for both columns but the second column has to unique to the unique values in the first column.

Example List

Fruit
Color
Apple

[Code]....

View 9 Replies View Related

Count Of Rows In Array That Meet Criteria In Column2 And Different Criteria In Column3

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

Compare Two Cells In A Row With Criteria. Count Rows That Match Criteria

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

Provide Filter With A List Of Criteria But When It Doesn't Match All Of The Criteria?

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

Multiple Criteria SUMIF Using Column As Negative Criteria?

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

Autofilter Macro: Goto The Next Criteria Down Until Certain Criteria Is Reached

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

Setup Sumif Statement With Two Criteria Where If Second Criteria Is True

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

Macro For AverageIFS With Multiple Criteria In Same Criteria Range

May 18, 2014

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

View 9 Replies View Related

Sum Only The Last Four Entries

Jan 21, 2009

I have a spreadsheet that i update weekly. i need is a formula that will just sum the last four weeks. Example column A rows 1-52 will be updated each week in row 53 i just want the last four weeks to be summed.

View 5 Replies View Related

Sum Only Same Entries

Jul 29, 2006

I would like help with a worksheet.

I have a column of Data which is a "material description".

In an adjacent column I have the square meterage of the material.

I would like to total all the square meterage of each material and have it shown in another column.

How can I do this?

I have searched far and wide for an example however I am probably using the wrong terms in my search.

Attached is a sample workbook.

View 9 Replies View Related

Sum Values Only If Adjacent Criteria Has Already Met Another Criteria On Another Sheet

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

Looking At Criteria In One Column And Creating List That Fit That Criteria

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

Remove Row Containing Criteria From List - Not Static Criteria

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

Lookup Value Using 2 Criteria - Criteria Moves Between Columns

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

Sum Range Based On 1 Criteria Of Column & 2 Criteria Of Another

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

Multiple Criteria Count Between 2 Dates & Other Criteria

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

Number Of Entries Above Certain Value

Aug 11, 2014

To give this a bit of context, I am looking at running speed of an athlete. I am trying to determine the NUMBER of sprints in a session (aka >15 km/hr for example). To do this, I need to calculate the number of times a speed vs time graph surpasses 15km/h.

For example, using this data:

TimeVelocity
0.212
0.413
0.614
0.815
116
1.217
1.416
1.615
1.814
214
2.212
2.410
2.613
2.814
315
3.216
3.417
3.618
3.817
415
4.214
4.413
4.612
4.812
511

I would calculate two sprints (one beginning at time point 0.8, one beginning at time point 3). I can't work out how to calculate these sprints independently, rather than counting the total number of entries above 15km/hr (11 in this example).

View 3 Replies View Related

Sum Last 5 Entries From Range

May 16, 2009

I have a row of golf scores "I3:X3" they either have a number value or "DNP".

I want to SUM only the last five number values and exclude the rest. The list will get a new entry every week. ie I want to SUM the last 5 most recent scores. There may be 15 or 16 scores or only 3, but I want the last 5 (or less) most recent.

View 6 Replies View Related

Formula To Add The Entries

Jun 12, 2009

If i have the following chart, what formula do i need to add all the entries EXCEPT and entry OS. i have the formula to add the entries but i need to exclude any "OS" entries. Like the following, but i want it to total 6. (minus the 2 OC entries)

=COUNTA(D6:D15)

TS
TS
VR
IS
IS
OS
VR
OS
8 (total)

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved