Get data based on multiple conditions

**efernandes67** · 12-31-2010, 09:08 AM

Hi,

See attached file.

My table has a 'Name', 'Start Date', 'End Date' and 'Nr of Days' columns. I need to get the data of the records found that matches the condtions shown on the file.

In this example, the number of records found is 3. What is the best way get tha data from this 3 records?

Regards,
Elio Fernandes

**DonkeyOte** · 12-31-2010, 09:11 AM

You can use COUNTIFS given use of XL2007

=COUNTIFS(Table1[Name],$I$3,Table1[sDate],"<="&$I$4,Table1[eDate],">="&$I$5)

change , to ; per your locale requirements - unknown

**efernandes67** · 12-31-2010, 10:16 AM

Hi,

I think I didn't explain to well.

I know how to count the number of records that match the conditions. You can see that in formula of
cell I7.

What I need is a way to retrieve all the data (Name, sDate, eDate, nDays) of the records that match the conditions..

Regrads,
Elio Fernandes

**DonkeyOte** · 12-31-2010, 10:36 AM

Regards the formula - understood though note that the COUNTIFS suggestion is significantly more efficient than the Array you were using.

If you need only to conduct one set of tests against your source data at any given time then you would be best served using formulae to generate the "x" denotations you have in your sample and in turn using this new denotation field as the Report Filter in a Pivot Table so as to retrieve the data, eg:

G3:
=REPT("x",AND($C3=$I$3,$D3<=$I$4,$E3>=$I$5))
given use of Table object the above should become part of the Table automatically when added

You can then create a Pivot off your Table object and use the above column as Report Filter (set to "x").
As and when you alter I3:I5 you would Refresh the Pivot to see updated data
Using formulae to generate the sub list though possible (and without too much difficulty) would IMO be reinventing the wheel to a certain extent.