SUMIF Excluding Hidden Rows

Hi all

Thanks in advance for your help - I've looked around this site and many others trying to get a solution for this but have been unable to find one; my apologies if it has been answered before.

Like most of you here I imagine I am self-taught with Excel so if I am not being clear with my description or using incorrect terms please bear with me and ask me to explain further.

Cheers

NB - I am using Excel 2003.

This is my issue:

I have a dataset that I have applied an autofilter too. Above this filtered table I have several rows applying several functions, such as average, sum, and count - I'll call these my 'Summing Rows'.

When I use the autofilter obviously some of the data gets hidden (as required). I want these 'Summing Rows' to only include the visible data. This is easy to do for a straight sum, I have just gone: SUBTOTAL(9,column to be summed).

Where is gets tricky and I can't figure it out is where I want to applying the following SUMIF formula to only non-filtered rows:

=SUMIF(C6:C13,"<>0",$B$6:$B$13)

This currently SUMS the figures in column B if the corosponding value in column C is not equal to 0.

I have included a workbook to further illustrate my point. The outcome I would expect is that when you filter the name column by say 'Jamie' the cells in red will read 10, 20, 20.

In its current form they read 50,70,70 because they are clearly summing the hidden cells also; so my question is essentially: How can I re-write this formula so it excludes the hidden rows when doing the SUMIF?

Thanks for your help with this!

I think I see what you mean but the reason I wanted to have the more complex bit in the formula (as opposed to selecting multiple filters) was because other people need to use this and I need to make it as simple as possible for them...