WOW, that first website (Bob Phillips-xldynamic) you sent me to was EXCELLENT
and gave a VERY thorough discussion of how to use SUMPRODUCT appropriately.
I have made some revisions to my formula, now that I understand how it works
and WHY. Thank you soooo very much. It works so much better when you know
WHY. I will definitely use this formula going forward.

Awesome!

I changed it to the below to catch all my nonzero, nonblank items that meet
my criteria in $C5:
=SUMPRODUCT(--('Repo Data'!$X$2:$X$4938<0),--('Repo
Data'!$X$2:$X$4938<""),--('Repo Data'!$AH$2:$AH$4938=$C5))
--
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

.