Complex lookup returning result of a formula

[orehovka]

Hello all!

I have two worksheets with partially overlapping data and I need to get a number of dates, that fall between two dates when certain criteria is met:
Sheet 1:

Sheet 2:

What I'm trying to do is:
- match a combination of cells in the first sheet, for example Red&Tree, with the same combination in the second sheet (not visible in the example, but available in the real document)
- then check what number of dates on the same row in Sheet 2 (from columns C:G) fall between Date 1 and Date 2 in Sheet 1.
I'm able to cover separately the matching of cells and number of dates calculation, but I can't combine these two criteria together.
Do you have any ideas how to solve this?

 

[Aladin Akyurek]

Sheet1

E1: Idx
E2, control+shift+enter, not just enter, and copy down:

=MATCH($A2,IF(Sheet2!$B$2:$B$6=$B2,Sheet2!$A$2:$A$6),0)

F1: Count
F2, control+shift+enter, not just enter, and copy down:

=SUM(IF(INDEX(Sheet2!$C$2:$G$6,$E2,0)>=INT($B2),
  IF(INDEX(Sheet2!$C$2:$G$6,$E2,0)<=INT($C2),1)))

Is this what you are after?

 

[orehovka]

Not exactly like this, but thank you for your suggestion, Aladin. I was trying to put these two operations in one formula.
I added another concatenated column in Sheet 2, so that I have already linked all combinations that generate dates to check. Now I'm able to match colors+type from Sheet 1 to Sheet 2 with vlookup or Match, but I still can't make it diplay my second formula and not the value of a cell.
For the comparison between dates I made up the following:
=SUMPRODUCT((Sheet2!$C$2:$F$2>B2)*(Sheet2!$C$2:$F$2)<c2)) but="" i="" want="" this="" check="" to="" be="" activated="" only="" when="" specific="" combination="" green+rock="" occurs="" in="" sheet="" 1.<="" html=""></c2))>

 

[Aladin Akyurek]

Not exactly like this, but thank you for your suggestion, Aladin. I was trying to put these two operations in one formula.
I added another concatenated column in Sheet 2, so that I have already linked all combinations that generate dates to check. Now I'm able to match colors+type from Sheet 1 to Sheet 2 with vlookup or Match, but I still can't make it diplay my second formula and not the value of a cell.
For the comparison between dates I made up the following:
=SUMPRODUCT((Sheet2!$C$2:$F$2>B2)*(Sheet2!$C$2:$F$2)<c2)) but="" i="" want="" this="" check="" to="" be="" activated="" only="" when="" specific="" combination="" green+rock="" occurs="" in="" sheet="" 1.<="" html=""></c2))>

Try to post the original exhibits in a form that is directly readable into Excel along with the desired results.