Help with SUMIF function

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
 
T

Toppers

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?
 
T

Toppers

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?
 
P

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
 
B

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)
 
T

Toppers

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top