complex count formulas using multiple criteria in different ranges

[Nutmeg007]

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$4938>0,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

 

[Bernard Liengme]

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/xlFunctions04.html#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

 

[Nutmeg007]

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?

 

[Nutmeg007]

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))

 

[Bernard Liengme]

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