Need help with SUM IF formula

[Ronen Toren]

I have set up the following formula: =SUM(IF(D221="IR",IF(C2:C21=53,1,0)))
it need to count when ever at D221 there is an IR and at C2:C21 there is
value of 53.
When checking the formula I get the currect results but at the excel sheet I
get different number - how come?

Pls contact ronen.toren for picture to better explainthe issue

 

[ehntd]

do a count if, that sounds like it would work better for you..

 

[Frank Kabel]

Hi
you have to enter this kind of formula as array formula with
CTRL+SHIFT+ENTER. After this the formula shoulb be shown enclosed with curly
brackets in the formula bar.

an alternative formula would be
=SUMPRODUCT(--(D221="IR"),--(C2:C21=53))

See for this:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html