need help COMPLEX SUM /w conditions
 

Hi there I need some help with the following problem...

I have a column

ColA ColB
2 test
3
Total
2 test
3


My total is in the same col as I'm trying to sum and I got the sum to
work like this
=SUM(INDIRECT("F1:F"&ROW()-1))+SUM(INDIRECT("F10000:F"&ROW()+1))

this will give me a sum before current row and after but of everything
now i need to make it conditional so that it only sums where col b =
to test... I had this working but without the big sum like this

SUMPRODUCT((I13:I35)*(H13:H35 = "test"))

can someone please help me combine these so I can sum one column with
totals being it the middle and also only sum based on a different
column relative content?please and thank you

Hi
try the following (not tested):
=SUMPRODUCT(--(OFFSET($B$1,0,0,ROW()-1)="Test"),OFFSET($B$1,0,0,ROW()-1
)) +
SUMPRODUCT(--(OFFSET($F$1,ROW(),0,10000-ROW())="Test"),OFFSET($F$1,ROW(
),0,10000-ROW()))

--
Regards
Frank Kabel
Frankfurt, Germany

"Seller" schrieb im Newsbeitrag
om...
Hi there I need some help with the following problem...

I have a column

ColA ColB
2 test
3
Total
2 test
3


My total is in the same col as I'm trying to sum and I got the sum to
work like this
=SUM(INDIRECT("F1:F"&ROW()-1))+SUM(INDIRECT("F10000:F"&ROW()+1))

this will give me a sum before current row and after but of
everything
now i need to make it conditional so that it only sums where col b =
to test... I had this working but without the big sum like this

SUMPRODUCT((I13:I35)*(H13:H35 = "test"))

can someone please help me combine these so I can sum one column with
totals being it the middle and also only sum based on a different
column relative content?please and thank you