Extracting record from multiple criteria

[tiong999]

Hello Dear Excell Master;

May someone assist me how to extracting data record base on multiple criteria.
I Found a good youtube channel which seems perfect similar issue i want to formulate, but seems dont work.

Here i attach is example book record from youtube sample.

I will grateful for your kindness observation why my result didnt work as it should in youtube.

Regards,
Tiong999

 

[bosco_yip]

Formula solution in yellow color background cells

1] Count
In C16 :
=SUMPRODUCT(($C$30:$C$39=$D$13)*ISNUMBER(MATCH($D$30:$D$39,$E$13:$E$14,0))*($E$30:$E$39=$F$13)*($B$30:$B$39>=$B$13)*($B$30:$B$39<=$C$13))

2] Output table
For excel 2010 or above
In K21, Non array formula copied across and down :
=IFERROR(INDEX(B$30:B$39,AGGREGATE(15,6,ROW($B$30:$B$39)-ROW($B$29)/($C$30:$C$39=$D$13)/ISNUMBER(MATCH($D$30:$D$39,$E$13:$E$14,0))/($E$30:$E$39=$F$13)/($B$30:$B$39>=$B$13)/($B$30:$B$39<=$C$13),ROW(A1))),"")

For excel 2007 or below
In K29, Array (CSE) formula copied across and down :
=IFERROR(INDEX(B$30:B$39,SMALL(IF(($C$30:$C$39=$D$13)*ISNUMBER(MATCH($D$30:$D$39,$E$13:$E$14,0))*($E$30:$E$39=$F$13)*($B$30:$B$39>=$B$13)*($B$30:$B$39<=$C$13),ROW($B$30:$B$39)-ROW($B$29)),ROW(A1))),"")

Regards
Bosco

 

[Peter Bartholomew]

I thought this looked interesting from a dynamic array viewpoint.

DA and traditional methods started out the same by checking each criterion. I could have combined the criteria in one but I chose to use defined names to hold a Boolean array corresponding to each field of the table, using the following formulae


The final formula combines the individual criteria for use in the FILTER function
= FILTER( Sales, combinedCriteria?, "Null" )
This formula typed into a single cell and committed with enter (no CSE) gives the entire result table.
Only the header row needs to be added.

I did also outline a more traditional solution using INDEX and the function SMALL.

= INDEX(Sales,selected, {1,2,3,4,5} )

 

[tiong999]

Formula solution in yellow color background cells

1] Count
In C16 :
=SUMPRODUCT(($C$30:$C$39=$D$13)*ISNUMBER(MATCH($D$30:$D$39,$E$13:$E$14,0))*($E$30:$E$39=$F$13)*($B$30:$B$39>=$B$13)*($B$30:$B$39<=$C$13))

For excel 2007 or below
In K29, Array (CSE) formula copied across and down :
=IFERROR(INDEX(B$30:B$39,SMALL(IF(($C$30:$C$39=$D$13)*ISNUMBER(MATCH($D$30:$D$39,$E$13:$E$14,0))*($E$30:$E$39=$F$13)*($B$30:$B$39>=$B$13)*($B$30:$B$39<=$C$13),ROW($B$30:$B$39)-ROW($B$29)),ROW(A1))),"")

Regards
Bosco

Dear Bosco,

Thanks again for solution...and i learned something today from you and Peter Bartholomew.


Best Regards,
Tiong

 

[rahulshewale1]

hii @tiong999 ,


See if is ok ?

Solution Using Advance Filter & Macro ( VBA )


Regard
Rahul shewale