SUMIF and hidden cells

[toci]

Hi folks,

Briefly, without example.
In cell C2 I have drop down menu: All, Top3, Top5 and Top10.
The data looks like (starts from row 7):

Please Login or Register  to view this content.

I have SUMIF formula =SUMIF($G$7:$G$155,"??9*",H7:H155) but the problem is when I filter data in C2 e.g. Top10. SUMIF doesn't work for filtered data. I am trying with SUMPRODUCT but still can't find the appropriate formula.

Any solution for this?

Thanks!

[mangesh.mehendale]

Finally Go through attached Workbook

[Tony Valko]

Maybe this...

=SUMPRODUCT(SUBTOTAL(9,OFFSET(H7,ROW(H7:H155)-ROW(H7),0)),--ISNUMBER(SEARCH("??9",G7:G155)))

[Tony Valko]

Finally Go through attached Workbook

Instead of hiding your suggestion in a file post it directly in the reply where everyone can see it.

[jason.b75]

Enter a subtotal formula in I7 and fill down

=SUBTOTAL(9,H7)+0

Then use the subtotal column in your sumif formula.

=SUMIF($G$7:$G$155,"??9*",I7:I155)

[toci]

Maybe this...

=SUMPRODUCT(SUBTOTAL(9,OFFSET(H7,ROW(H7:H155)-ROW(H7),0)),--ISNUMBER(SEARCH("??9",G7:G155)))

That's it! Works. I just replaced 9 with 109!

Thank you very much!

[Tony Valko]

You're welcome. Thanks for the feedback!