Countif except hidden (filtered) cells

HI,

I'm looking for a way to do the following:
suppose I have on sheet2 in column A several words or numbers and in column B several values and in colum C an identifier:

Sheet 2
A B C
ford 100 y
audi 200 y
ford 150 n
mcl 300 y
audi 100 y
ford 100 y

on sheet 1 I have in column A several brands and in column B I would like to have totals (sums) of those brands.
However, a filter is applied (for example on column C=y). I do not want to count the hidden (filtered) rows.
So the result should be

A B
ford 200
audi 300
mcl 300

I've searched several forums and found a great working solution for that:

=SUMPRODUCT(--(CondRange="Cond"),SUBTOTAL(9;OFFSET(RangeToSum; ROW(RangeToSum)-MIN(ROW(RangeToSum));0;1)))

where:
CondRange is the range where conditions are to be searched (sheet2!A1:A6)
Cond is the condition that must be met (i.e. sheet1!A1 ("ford"))
RangeToSum is the range that has to be summed (sheet2!B1:B6)

This works like a charm. Only there are also other people working with this sheet who don't understand *** about excel, therefore I would like to simplify this by creating a VBA code.

This is what I have: (subsumif because it is a combination of sumif and subtotal )

Function SubSumIf(CondRange As Range, Cond As Range, SumRange As Range)
'SumIf in combination with Subtotal is nonexistent formula in excel. That is why this formula is created
'=SUMPRODUCT(--(CondRange="Cond"),SUBTOTAL(9;OFFSET(RangeToSum; ROW(RangeToSum)-MIN(ROW(RangeToSum));0;1)))

variables = "--(" & CondRange.Address(external:=True) & "=" & Cond.Address(external:=True) & ");SUBTOTAL(9;OFFSET(" & SumRange.Address(external:=True) & ";ROW(" & SumRange.Address(external:=True) & ")-MIN(ROW(" & SumRange.Address(external:=True) & "));0;1))"
SubSumIf = Application.WorksheetFunction.SumProduct(variables)
End Function

variables is the exact text that is used when typing the long formula in a cell.
However, VBA should work with ranges. but I can not figure out how to use that in this particular formula.

Does anyone have an idea how to do that?
Someone must have done this before?

Thanks for any answers!

Kind Regards

Serge

PS: i'm still using excel 2002 :s

Hi Jakobshavn,
Thanks for your reply.
Indeed this would pose a solution but it would also increase the number of columns, which I prefer not to do.
The sheet I'm actually using is way more advanced than the problem I'm describing for now, so I would like to keep it as dummy proof as possible (on the excell side anyway )
So, if there are possibilities, i would like to try the simple function without added columns.
Thanks anyway!

greetings!