Formula help

Kiwi-123
Kiwi-123 used Ask the Experts™
on
Please can you help me amend the formula on sheet 2 (D7:D8)

The formula counts the occurence on sheet 1, but I wish to make the date range it searches for to be:- sheet 2 (D3:D4)
new.xls
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
For the Number in cell D7, you might try:
=SUMPRODUCT((Sheet1!B$8:B$103>=D$3)*(Sheet1!B$8:B$103<=D$4)*SUBTOTAL(2,OFFSET(Sheet1!C$8,ROW(Sheet1!C$8:C$103)-ROW(Sheet1!C$8),0)),Sheet1!C$8:C$103)

And for the Count of Yes in cell D8, you might try:
=SUMPRODUCT((Sheet1!B$8:B$103>=D$3)*(Sheet1!B$8:B$103<=D$4)*(Sheet1!D$8:D$103="Y")*SUBTOTAL(2,OFFSET(Sheet1!C$8,ROW(Sheet1!C$8:C$103)-ROW(Sheet1!C$8),0)))

Both of these formulas are looking at visible rows only

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial