Help w/ counting multiple columns based on IF criteria

I am trying to figure out how to count an occurence if a condition in one
column is true and if a condition in another column is true as well. Here is
an example:

A B C D
Subdivision Presold Spec Model
BANKER'S HAVEN x
INVESTOR'S HOLLOW x
BUILDER'S ESTATES x

I want to count everytime the subdivision = "Banker's Haven" and "Presold".
I have racked my brain and tried everything. part of the problem is that the
information is provided by external clients and I do not have control of the
format. It is likely that all of the information comes in on 1 tab and may
have a couple thousand units.

Can someone help me? I have tried nested IF formulas..IF AND formulas which
doesn't seem to work b/c the comparison data is in two seperate columns. I've
tried SUMIF, COUNTIF, you name it..Anyone have any solutions?

Thanks for your help.
James

Assuming Presold is signified by an "x" in column B, then

=sumproduct(--(a2:a1000="Banker's Haven"),--(b2:B1000="x"))


"ConstructionGuy" wrote:

> I am trying to figure out how to count an occurence if a condition in one
> column is true and if a condition in another column is true as well. Here is
> an example:
>
> A B C D
> Subdivision Presold Spec Model
> BANKER'S HAVEN x
> INVESTOR'S HOLLOW x
> BUILDER'S ESTATES x
>
> I want to count everytime the subdivision = "Banker's Haven" and "Presold".
> I have racked my brain and tried everything. part of the problem is that the
> information is provided by external clients and I do not have control of the
> format. It is likely that all of the information comes in on 1 tab and may
> have a couple thousand units.
>
> Can someone help me? I have tried nested IF formulas..IF AND formulas which
> doesn't seem to work b/c the comparison data is in two seperate columns. I've
> tried SUMIF, COUNTIF, you name it..Anyone have any solutions?
>
> Thanks for your help.
> James
>

Duke--you are awesome! Thanks a million!
James

"Duke Carey" wrote:

> Assuming Presold is signified by an "x" in column B, then
>
> =sumproduct(--(a2:a1000="Banker's Haven"),--(b2:B1000="x"))
>
>
> "ConstructionGuy" wrote:
>
> > I am trying to figure out how to count an occurence if a condition in one
> > column is true and if a condition in another column is true as well. Here is
> > an example:
> >
> > A B C D
> > Subdivision Presold Spec Model
> > BANKER'S HAVEN x
> > INVESTOR'S HOLLOW x
> > BUILDER'S ESTATES x
> >
> > I want to count everytime the subdivision = "Banker's Haven" and "Presold".
> > I have racked my brain and tried everything. part of the problem is that the
> > information is provided by external clients and I do not have control of the
> > format. It is likely that all of the information comes in on 1 tab and may
> > have a couple thousand units.
> >
> > Can someone help me? I have tried nested IF formulas..IF AND formulas which
> > doesn't seem to work b/c the comparison data is in two seperate columns. I've
> > tried SUMIF, COUNTIF, you name it..Anyone have any solutions?
> >
> > Thanks for your help.
> > James
> >

Hi,

You may also try this array formula (Ctrl+Shift+Enter)

=sum(if((range1="BANKER'S HAVEN")*(range2="x"),1,0))

Regards,

Ashish Mathur

"ConstructionGuy" wrote:

> I am trying to figure out how to count an occurence if a condition in one
> column is true and if a condition in another column is true as well. Here is
> an example:
>
> A B C D
> Subdivision Presold Spec Model
> BANKER'S HAVEN x
> INVESTOR'S HOLLOW x
> BUILDER'S ESTATES x
>
> I want to count everytime the subdivision = "Banker's Haven" and "Presold".
> I have racked my brain and tried everything. part of the problem is that the
> information is provided by external clients and I do not have control of the
> format. It is likely that all of the information comes in on 1 tab and may
> have a couple thousand units.
>
> Can someone help me? I have tried nested IF formulas..IF AND formulas which
> doesn't seem to work b/c the comparison data is in two seperate columns. I've
> tried SUMIF, COUNTIF, you name it..Anyone have any solutions?
>
> Thanks for your help.
> James
>