Help with SUMIF function

[PO]

Hi,

I posted a problem concerning the SUMIF formula yesterday. The problem has
slighly changed.
I want the SUMIF formula to add the values in KolA only if the sum of
KolA-(KolB + KolC) is greater then 0. The comparison is to be down for each
row.
If I, for example, have the following numbers:

Kol A Kol B KolC
100 25 30
60 40 30
25 20 15

In the above example the SUMIF formula should return 85.
I need to use the SUMIF formula since the result should only be calculated
for rows not hidden by the autofilter function.

Regards
PO

 

[Guest]

Try:

=SUM(IF(A2-(B2+C2)>0,A2)) entered as an array formula, using
Ctrl+Shift+Enter which will place curly brackets {} rounf the formula.

And shouldn't the answer be 100?

 

[Guest]

sorry ... try:

=SUM(IF(A2:A4-(B2:B4+C2:C4)>0,A2:A4)) entered as an array formula, using
Ctrl+Shift+Enter which will place curly brackets {} rounf the formula.

And shouldn't the answer be 100?

 

[PO]

Hi,
Thanks for your answer. You're right the result should be 100.
The problem with your formula is that it doesn't work together with
autofilter. I have to use SUMIF because filtered rows should not be part of
the calculation..

Regards
PO

 

[Guest]

Try:

=SUMPRODUCT(--(A2:A4-(B2:B4+C2:C4)>0),--(A2:A4))

 

[Bob Phillips]

=SUMPRODUCT(SUBTOTAL(3,OFFSET(A1,ROW($A$2:$A$10)-ROW($A$1),,1)),
--(A2:A10-(B2:B10+C2:C10)>0),A2:A10)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

I tried SUMIF with filtered data and filtered rows were included in the SUM
(unless I misunderstand your need).