Help with SUMIF function

  • Thread starter Thread starter PO
  • Start date Start date
P

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
 
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?
 
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?
 
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
 
=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)
 
I tried SUMIF with filtered data and filtered rows were included in the SUM
(unless I misunderstand your need).
 
Back
Top