PDA

View Full Version : [SOLVED] Formula - Sumif,sumifs, aggregate



Marcster
01-15-2016, 02:39 AM
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
01-15-2016, 03:19 AM
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
01-15-2016, 03:40 AM
Hello Marcster,

A simpler and more flexible formula is


=Aggregate(9,5,C2:C13)

Marcster
01-15-2016, 06:45 AM
Yep, this works: =Aggregate(9,5,C2:C13) Thanks Leith Ross, did look at Aggregate but had 3 instead of 5.