Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filter data with two criterias
How do I filter data which fall between two dates(variable) with two criteria.
Data is entered in rows (4 to 100). Row 3 are all the headings (A to G). Date from= cell A2. Date to= cell B2 Cell A4 = date Cell B4 = criteria 1 Cell C3 = criteria 2 Cell G4 = number to extract. Result to appear in cell A4. Thank you -- Geo |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filter data with two criterias
Your problem doesn't give a unique answer. You probably have more than one
results. I would add a new column that will give a yes/no results or anything else. Put a formmula like this in the new column =if(and(a4 = $A$2,A4 <= $B$2,G4 = B4,G4 <= C4),"Yes","No") A date is a number in excel so A4 need to be checked if it is greater than the start date and less than the end date. I think you want criteria 2 to be in cell C4 not C3. Insert formula in new cell H4 and then copy down to cells C5:C100 "Geo" wrote: How do I filter data which fall between two dates(variable) with two criteria. Data is entered in rows (4 to 100). Row 3 are all the headings (A to G). Date from= cell A2. Date to= cell B2 Cell A4 = date Cell B4 = criteria 1 Cell C3 = criteria 2 Cell G4 = number to extract. Result to appear in cell A4. Thank you -- Geo |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filter data with two criterias
Thanks Joel,
I have been thinking about this, maybe I can tackle it another way. I have the following formula in a table: =SUMPRODUCT(--($B$4:$B$100=$B106),--($C$4:$C$100=C$105),$G$4:$G$100) How do I insert in the formula, to look for data between two dates. The dates will be in cells Date from= cell A2. Date to= cell B2 and the date column on the table is column A. -- Geo "Joel" wrote: Your problem doesn't give a unique answer. You probably have more than one results. I would add a new column that will give a yes/no results or anything else. Put a formmula like this in the new column =if(and(a4 = $A$2,A4 <= $B$2,G4 = B4,G4 <= C4),"Yes","No") A date is a number in excel so A4 need to be checked if it is greater than the start date and less than the end date. I think you want criteria 2 to be in cell C4 not C3. Insert formula in new cell H4 and then copy down to cells C5:C100 "Geo" wrote: How do I filter data which fall between two dates(variable) with two criteria. Data is entered in rows (4 to 100). Row 3 are all the headings (A to G). Date from= cell A2. Date to= cell B2 Cell A4 = date Cell B4 = criteria 1 Cell C3 = criteria 2 Cell G4 = number to extract. Result to appear in cell A4. Thank you -- Geo |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filter data with two criterias
=SUMPRODUCT(--($A$4:$A$100=$A$2),--($A$4:$A$100<=$B$2),--($B$4:$B$100=$B106),--($C$4:$C$100=C$105),$G$4:$G$100)
Adjust the = and <= to and < if applicable. -- David Biddulph "Geo" wrote in message ... Thanks Joel, I have been thinking about this, maybe I can tackle it another way. I have the following formula in a table: =SUMPRODUCT(--($B$4:$B$100=$B106),--($C$4:$C$100=C$105),$G$4:$G$100) How do I insert in the formula, to look for data between two dates. The dates will be in cells Date from= cell A2. Date to= cell B2 and the date column on the table is column A. -- Geo "Joel" wrote: Your problem doesn't give a unique answer. You probably have more than one results. I would add a new column that will give a yes/no results or anything else. Put a formmula like this in the new column =if(and(a4 = $A$2,A4 <= $B$2,G4 = B4,G4 <= C4),"Yes","No") A date is a number in excel so A4 need to be checked if it is greater than the start date and less than the end date. I think you want criteria 2 to be in cell C4 not C3. Insert formula in new cell H4 and then copy down to cells C5:C100 "Geo" wrote: How do I filter data which fall between two dates(variable) with two criteria. Data is entered in rows (4 to 100). Row 3 are all the headings (A to G). Date from= cell A2. Date to= cell B2 Cell A4 = date Cell B4 = criteria 1 Cell C3 = criteria 2 Cell G4 = number to extract. Result to appear in cell A4. Thank you -- Geo |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filter data with two criterias
Thanks guys,
I wanted the formula that sums the totals up. It works fine now. Great stuff -- Geo "Sandy Mann" wrote: The problem with using SUMPRODUCT() is that ,as Joel pointed out, there is a possibility that the OP's data may meet more than one set of date/criteria. If that were to be the case then SUMPRODUCT() will add all the matching values in Column G. Of course that may well be what the OP wants but if, by any chance, he wants only the first match returned then the only thing that I can come up with is a UDF: Option Explicit Function FilterData() Dim x As Long Dim G For x = 4 To 100 If Cells(x, 1).Value2 = Cells(1, 1).Value2 Then If Cells(x, 1).Value2 <= Cells(1, 2).Value2 Then If Cells(x, 2).Value = Cells(1, 3).Value Then If Cells(x, 3).Value = Cells(1, 4).Value Then G = Cells(x, 7).Value Exit For End If End If End If End If Next x FilterIt = G End Function -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... =SUMPRODUCT(--($A$4:$A$100=$A$2),--($A$4:$A$100<=$B$2),--($B$4:$B$100=$B106),--($C$4:$C$100=C$105),$G$4:$G$100) Adjust the = and <= to and < if applicable. -- David Biddulph "Geo" wrote in message ... Thanks Joel, I have been thinking about this, maybe I can tackle it another way. I have the following formula in a table: =SUMPRODUCT(--($B$4:$B$100=$B106),--($C$4:$C$100=C$105),$G$4:$G$100) How do I insert in the formula, to look for data between two dates. The dates will be in cells Date from= cell A2. Date to= cell B2 and the date column on the table is column A. -- Geo "Joel" wrote: Your problem doesn't give a unique answer. You probably have more than one results. I would add a new column that will give a yes/no results or anything else. Put a formmula like this in the new column =if(and(a4 = $A$2,A4 <= $B$2,G4 = B4,G4 <= C4),"Yes","No") A date is a number in excel so A4 need to be checked if it is greater than the start date and less than the end date. I think you want criteria 2 to be in cell C4 not C3. Insert formula in new cell H4 and then copy down to cells C5:C100 "Geo" wrote: How do I filter data which fall between two dates(variable) with two criteria. Data is entered in rows (4 to 100). Row 3 are all the headings (A to G). Date from= cell A2. Date to= cell B2 Cell A4 = date Cell B4 = criteria 1 Cell C3 = criteria 2 Cell G4 = number to extract. Result to appear in cell A4. Thank you -- Geo |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Too many criterias... | Excel Worksheet Functions | |||
If Criterias | Excel Discussion (Misc queries) | |||
in data/filter/auto filter | Excel Worksheet Functions | |||
"Criteria Range" in the "Data/Filter/Advanced Filter" to select Du | Excel Worksheet Functions | |||
Multiple Data Validation Criterias | Excel Worksheet Functions |