complex count formulas using multiple criteria in different ranges
 

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

complex count formulas using multiple criteria in different ranges
 

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

complex count formulas using multiple criteria in different ra
 

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

.

complex count formulas using multiple criteria in different ra
 

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

.

complex count formulas using multiple criteria in different ra
 

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

.