Help with SUMIF function
 

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

Help with SUMIF function
 

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?

"PO" wrote:

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

Help with SUMIF function
 

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?


"Toppers" wrote:

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?

"PO" wrote:

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

Help with SUMIF function
 

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


"Toppers" skrev i meddelandet
...
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?

"PO" wrote:

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

Help with SUMIF function
 

Try:

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

"PO" wrote:

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


"Toppers" skrev i meddelandet
...
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?

"PO" wrote:

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

Help with SUMIF function
 

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

"PO" <h wrote in message ...
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


"Toppers" skrev i meddelandet
...
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?

"PO" wrote:

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

Help with SUMIF function
 

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

"Toppers" wrote:

Try:

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

"PO" wrote:

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


"Toppers" skrev i meddelandet
...
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?

"PO" wrote:

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