SUMIF function help

[PO]

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

 

[SteveG]

PO,

Try,

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

Does that help?

Stev

 

[Bob Phillips]

=SUMPRODUCT(--(A1:A3>(C1:C3-D13-E1:E3)),B1:B3)

--
HTH

Bob Phillips

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

 

[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

 

[Bob Phillips]

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)