I overlooked the part about braces {}
These show that the formula was entered using CTRL + SHIFT + ENTER because
it is an array formula
Learn more about this topic at
http://www.cpearson.com/excel/ArrayFormulas.aspx

Glad you enjoyed leaning about SUMPRODUCT
best wishes
--
Bernard Liengme
http://people.stfx.ca/bliengme
Microsoft Excel MVP

"Nutmeg007" wrote in message
...
no, I am using EXCEL 2003. I will go become familar with the SUMPRODUCT
references you sent me...thanks

so you don't have any insight on the use (& disappearance) of those { }
brackets on the existing Sum(IF( formula?
--
Thanks for your help



"Bernard Liengme" wrote:

You will find it very helpful to become familiar with SUMPRODUCT

Bob Phillips
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
J.E McGimpsey
http://mcgimpsey.com/excel/formulae/doubleneg.html
Debra Dalgleish
http://www.contextures.com/xlFunctio...tml#SumProduct


Are you using Excel 2007? If so, then read help on SUMIFS

Please come back when you have looked at these so we can help you further
best wishes

--
Bernard Liengme
http://people.stfx.ca/bliengme
Microsoft Excel MVP


"Nutmeg007" wrote in message
...
I am trying to calculate a count, if a range of cells meets multiple
criteria. This formula is in an existing worksheet that I inherited
and
it
works as it currently exists to give me the count of items that meet
the
criteria of being both (1) account type # in cell range $U= "50000" and
the
value in Cell range $X is a value (ie is not blank or negative). See
formula
below plus an explanation of my problem.

{=SUM(IF('Repo Data'!$X$2:$X$4938=0,IF('Repo
Data'!$U$2:$U$4938="50000",1)))+SUM(IF('Repo
Data'!$X$2:$X$49380,IF('Repo
Data'!$U$2:$U$4938="50000",1)))-SUM(IF('Repo
Data'!$X$2:$X$4938="",IF('Repo
Data'!$U$2:$U$4938="50000",1)))}

However, I am trying to modify it using different criteria (Legal
entity)
in
cell range $AH instead of (account type) in cell range $U.......
but when I try to edit the formula to replace the "50000" with another
value
or a cell reference, (and change the column reference range
accordingly)
it
doesn't work, and the {} brackets on either end of the formula go
away....I
have never seen these brackets used this way, and don't know why they
are
on
the formula, but they seem to make a diff.

any thoughts on this above problem?

...also as an alternative I tried the below

=SUM(IF(AND('Repo Data'!$O$2:$O$4938=0.01,'Repo
Data'!$AH$2:$AH$4938=C5),1)) Doesn't work

=SUM(IF(AND('Repo Data'!$O$2:$O$4938=0.01,'Repo
Data'!$AH$2:$AH$4938="HAF"),1)) Doesn't work

the below alternative formula works, but seems like such a roundabout
way
to
get the answer....and I had to put in the -0.01 and the <=60000 as
place
holders as the values I want should be positive and would never be
greater
than 30,000 but I used 60,000 just to be sure. I tried using 0.00 but
that
didn't work...it needed the -0.01 to work.

=SUMPRODUCT(--('Repo Data'!$O$2:$O$4938-0.01),--('Repo
Data'!$O$2:$O$4938<=60000),--('Repo Data'!$AH$2:$AH$4938=C5))

any insight you can provide would be appreciated

.