SUMIF function help
 

Hi

Normaly you use the sumif function to check if a value in one cell is
greater then a certain value, e.g.:
=SUMIF(A1:A3,"0",B1:B3)

I, however want to check if one cells value is greater then the sum of a
cellrange, e.g.:
=SUMIF(A1:A3,"=C1:C3-Sum(D1:E3)", B1:B3)

I could of course use an empty column and calculate the difference (e.g.
C1-sum(D1:E1)) and then use the SUMIF formula to check however the value in
that cell is less or greater then 0 but I would rather acomplish this with
one formula.

Is this possible (perhaps using another array formula)?

Regards
PO

SUMIF function help
 

PO,

Try,

=SUMIF(A1:A3,"="&SUM(C1:C3)-SUM(D1:E3),B1:B3)

Does that help?

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=547152

SUMIF function help
 

=SUMPRODUCT(--(A1:A3(C1:C3-D1:D3-E1:E3)),B1:B3)

--
HTH

Bob Phillips

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

"PO" <h wrote in message ...
Hi

Normaly you use the sumif function to check if a value in one cell is
greater then a certain value, e.g.:
=SUMIF(A1:A3,"0",B1:B3)

I, however want to check if one cells value is greater then the sum of a
cellrange, e.g.:
=SUMIF(A1:A3,"=C1:C3-Sum(D1:E3)", B1:B3)

I could of course use an empty column and calculate the difference (e.g.
C1-sum(D1:E1)) and then use the SUMIF formula to check however the value
in
that cell is less or greater then 0 but I would rather acomplish this with
one formula.

Is this possible (perhaps using another array formula)?

Regards
PO

SUMIF function help
 

Thanks both of you!

Bob Phillips formula worked better in this case. I still don't understand
why the two minus signs in the beginning are needed, but the formula won't
work without them so I guess I leave them there :)

Regards
PO


"PO" <h skrev i meddelandet ...
Hi

Normaly you use the sumif function to check if a value in one cell is
greater then a certain value, e.g.:
=SUMIF(A1:A3,"0",B1:B3)

I, however want to check if one cells value is greater then the sum of a
cellrange, e.g.:
=SUMIF(A1:A3,"=C1:C3-Sum(D1:E3)", B1:B3)

I could of course use an empty column and calculate the difference (e.g.
C1-sum(D1:E1)) and then use the SUMIF formula to check however the value
in that cell is less or greater then 0 but I would rather acomplish this
with one formula.

Is this possible (perhaps using another array formula)?

Regards
PO

SUMIF function help
 

See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed
explanation.

--
HTH

Bob Phillips

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

"PO" <h wrote in message ...
Thanks both of you!

Bob Phillips formula worked better in this case. I still don't understand
why the two minus signs in the beginning are needed, but the formula won't
work without them so I guess I leave them there :)

Regards
PO


"PO" <h skrev i meddelandet ...
Hi

Normaly you use the sumif function to check if a value in one cell is
greater then a certain value, e.g.:
=SUMIF(A1:A3,"0",B1:B3)

I, however want to check if one cells value is greater then the sum of a
cellrange, e.g.:
=SUMIF(A1:A3,"=C1:C3-Sum(D1:E3)", B1:B3)

I could of course use an empty column and calculate the difference (e.g.
C1-sum(D1:E1)) and then use the SUMIF formula to check however the value
in that cell is less or greater then 0 but I would rather acomplish this
with one formula.

Is this possible (perhaps using another array formula)?

Regards
PO