Using sumif with subtotal

[Robert]

Lights 12,505.00
Solar 23,622.44
Lights 21,771.00
Solar 23,622.44
Lights 23,739.00
Solar 23,622.44
Lights 9,594.00
Solar 23,622.44

Using above data as example, how do I subtotal using sumif where data has
been filtered in a larger database? The filtered data base has returned data
that has already been correctly subtotalled using function_num 9. This list
now contains hidden rows. I now wish to further subtotal the list using
criteria lights or solar in the sumif?

 

[Robert]

To give a further idea of what I'm attempting to achieve:

=sumif(c3:c191,"Lights",subtotal(9,d3:d191)) - doesn't work.

I use Excel 2007.

 

[T. Valko]

Try this:

Lights/Solar in column B
Amounts in column C

Full unfiltered range is B2:C12

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B12,ROW(B2:B12)-ROW(B2),0,1)),(B2:B12="Lights")+(B2:B12="Solar"),C2:C12)

 

[Robert]

Thanks for your help thus far.

Now what if I just want solar? How would the formula loo please?

 

[T. Valko]

Just remove the test for Lights:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B12,ROW(B2:B12)-ROW(B2),0,1)),--(B2:B12="Solar"),C2:C12)

And, if just want Lights:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B12,ROW(B2:B12)-ROW(B2),0,1)),--(B2:B12="Lights"),C2:C12)

 

[Robert]

Thanks Biff. Just one more if I may. What if there is numerical data in
adjacent cells (say, F, G and H, etc) that continue to the right?

 

[T. Valko]

What about it?

How does it relate?

 

[Robert]

Presently we have dealt with data in columns B and C. There is additional
data in columns D and beyond. What I am attempting to find out is what the
formula would be for cells D and so on? What part of the Formula changes as
it is copied across the spreadsheet? I realise that certain parts of the
formula will remian absolute.

Thanks.

 

[T. Valko]

Oh, OK!

If you want to sum column C, D, E, F, etc., just make the reference to
column B absolute and the reference to the sum column relative:

=SUMPRODUCT(SUBTOTAL(3,OFFSET($B2:$B12,ROW($B2:$B12)-ROW($B2),0,1)),($B2:$B12="Lights")+($B2:$B12="Solar"),C2:C12)