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

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

=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

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

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