Formula - Sumif,sumifs, aggregate

[Marcster]

Hi People,

I have the below in a Table:

ColA	ColB	ColC
A	2.5	2.5
B	5	5
A	6	6
A	200	0
B	2	2
C	36	36
D	150	0
E	56.96	56.96
F	8.9	8.9
E	125	0
D	5	5
D	8	8

I'm after a formula that will Sum ColB where ColC <> o and this result will change to only include visible rows when ColA is filtered.

Thanks,

[mancubus]

is it?

=SUMPRODUCT(SUBTOTAL(9,OFFSET($B$2:$B$1000,ROW($B$2:$B$1000)-ROW($B2),0,1)),--($C$2:$C$1000<>0)) 

thanks Google

[Leith Ross]

Hello Marcster,

A simpler and more flexible formula is

   =Aggregate(9,5,C2:C13)

[Marcster]

Yep, this works: =Aggregate(9,5,C2:C13) Thanks Leith Ross, did look at Aggregate but had 3 instead of 5.