Drag formula to entire column with ignoring hidden rows

copy and paste ignores hidden cells, which works in most cases

however to get your situation to work where A1 =SUMMARY!J7, A2:A14 (hidden), A15 =SUMMARY!J8

instead of trying to figure out a complicated cell formula which works when copied, you're better off going through a sorting process

1. if you get lost close and don't save so you can start over.
2. un-filter your data
3. Insert two columns to the left of the formula one (column A in your example)
4. Fill the first colum with a counting formula (A1 =1, A2:A3000 =A1+1)
5. copy and paste the first colum by values over the top of itself to preserve the original row order.
6. fill the second column with a filter formula that matches how you had it filtered(B1:B3000 =AND(D1="New",OR(G1="Larry",G1="Curly",G1="Moe")))
7. select the whole data range including the two new columns you just added
8. sort by the second column descending because in Excel TRUE < FALSE (sort by B descending)
9. Filter for TRUE
10. Fill your formula in as you would have done before by dragging it down
11. Sort by the first column since it preserved the original order
12. remove the two helper columns you created
13. re-filter your data as you originally had it

It's a bit of a twiddle, but it's kinda fast once you get the hang of it.