Filter data with two criterias

[Guest]

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

 

[Guest]

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

 

[Guest]

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.

 

[David Biddulph]

=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.

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Guest]

Thanks guys,
I wanted the formula that sums the totals up.
It works fine now.
Great stuff