Using "COUNTIF" and "AND" functions together
 

I have an array of stores in various geographies and am trying to count the
number of stores in a particular "Market" that also have "Restrooms". I'm
essentially trying to get a count of how many stores in each "market" have
"restrooms". For instance, I have 250 stores, 5 of which are in Atlanta and
among those 5, only 2 have Restrooms. I'd like to use this function for
every geography so that next to my 20 geography's I can include a column that
shows how many "restrooms" in each geography.. Any help would be greatly
appreciated.

Using "COUNTIF" and "AND" functions together
 

Try
=SUMPRODUCT(--(A2:A10="geography"),--(B2:B10="restroom"))

If this post helps click Yes
---------------
Jacob Skaria


"trevor" wrote:

I have an array of stores in various geographies and am trying to count the
number of stores in a particular "Market" that also have "Restrooms". I'm
essentially trying to get a count of how many stores in each "market" have
"restrooms". For instance, I have 250 stores, 5 of which are in Atlanta and
among those 5, only 2 have Restrooms. I'd like to use this function for
every geography so that next to my 20 geography's I can include a column that
shows how many "restrooms" in each geography.. Any help would be greatly
appreciated.

Using "COUNTIF" and "AND" functions together
 

Jacob,
A couple of questions:
1) what does the "--" do? I've never used them before in a function
2) if I want to reference a cell with "Atlanta" in the Geography column
instead of typing "Atlanta" does the formula change?
thanks a ton

"Jacob Skaria" wrote:

Try
=SUMPRODUCT(--(A2:A10="geography"),--(B2:B10="restroom"))

If this post helps click Yes
---------------
Jacob Skaria


"trevor" wrote:

I have an array of stores in various geographies and am trying to count the
number of stores in a particular "Market" that also have "Restrooms". I'm
essentially trying to get a count of how many stores in each "market" have
"restrooms". For instance, I have 250 stores, 5 of which are in Atlanta and
among those 5, only 2 have Restrooms. I'd like to use this function for
every geography so that next to my 20 geography's I can include a column that
shows how many "restrooms" in each geography.. Any help would be greatly
appreciated.

Using "COUNTIF" and "AND" functions together
 

Hi Trevor

1) -- converts boolean TRUE/FALSE to 1 and 0...for the sumproduct..You can
find an explanasion here http://mcgimpsey.com/excel/formulae/doubleneg.html
You can also use

=SUMPRODUCT((A2:A10="geography")*(B2:B10="restroom "))

2) You can reference to a cell with the query fields in C1. The array rows
needs to be same for Col A and Col B...So you can have a list of geographies
and beside you can place the formula and copy down as required...

=SUMPRODUCT(($A$2:$A$100=C1)*($B$2:$B$100="restroo m"))

If this post helps click Yes
---------------
Jacob Skaria


"trevor" wrote:

Jacob,
A couple of questions:
1) what does the "--" do? I've never used them before in a function
2) if I want to reference a cell with "Atlanta" in the Geography column
instead of typing "Atlanta" does the formula change?
thanks a ton

"Jacob Skaria" wrote:

Try
=SUMPRODUCT(--(A2:A10="geography"),--(B2:B10="restroom"))

If this post helps click Yes
---------------
Jacob Skaria


"trevor" wrote:

I have an array of stores in various geographies and am trying to count the
number of stores in a particular "Market" that also have "Restrooms". I'm
essentially trying to get a count of how many stores in each "market" have
"restrooms". For instance, I have 250 stores, 5 of which are in Atlanta and
among those 5, only 2 have Restrooms. I'd like to use this function for
every geography so that next to my 20 geography's I can include a column that
shows how many "restrooms" in each geography.. Any help would be greatly
appreciated.

Using "COUNTIF" and "AND" functions together
 

great... the second option without the "--" works. I'll have to read about
the "--". this second option only works if I change the cell from "Yes" in
the restroom column to "1" for restroom, "0" for no restroom. I'm guessing
the "--" may help solve that? would I need to download a toolpak to make the
"--" work?
thanks again!

"Jacob Skaria" wrote:

Hi Trevor

1) -- converts boolean TRUE/FALSE to 1 and 0...for the sumproduct..You can
find an explanasion here http://mcgimpsey.com/excel/formulae/doubleneg.html
You can also use

=SUMPRODUCT((A2:A10="geography")*(B2:B10="restroom "))

2) You can reference to a cell with the query fields in C1. The array rows
needs to be same for Col A and Col B...So you can have a list of geographies
and beside you can place the formula and copy down as required...

=SUMPRODUCT(($A$2:$A$100=C1)*($B$2:$B$100="restroo m"))

If this post helps click Yes
---------------
Jacob Skaria


"trevor" wrote:

Jacob,
A couple of questions:
1) what does the "--" do? I've never used them before in a function
2) if I want to reference a cell with "Atlanta" in the Geography column
instead of typing "Atlanta" does the formula change?
thanks a ton

"Jacob Skaria" wrote:

Try
=SUMPRODUCT(--(A2:A10="geography"),--(B2:B10="restroom"))

If this post helps click Yes
---------------
Jacob Skaria


"trevor" wrote:

I have an array of stores in various geographies and am trying to count the
number of stores in a particular "Market" that also have "Restrooms". I'm
essentially trying to get a count of how many stores in each "market" have
"restrooms". For instance, I have 250 stores, 5 of which are in Atlanta and
among those 5, only 2 have Restrooms. I'd like to use this function for
every geography so that next to my 20 geography's I can include a column that
shows how many "restrooms" in each geography.. Any help would be greatly
appreciated.

Using "COUNTIF" and "AND" functions together
 

See this:

http://xldynamic.com/source/xld.SUMPRODUCT.html

--
Biff
Microsoft Excel MVP


"trevor" wrote in message
...
great... the second option without the "--" works. I'll have to read
about
the "--". this second option only works if I change the cell from "Yes"
in
the restroom column to "1" for restroom, "0" for no restroom. I'm guessing
the "--" may help solve that? would I need to download a toolpak to make
the
"--" work?
thanks again!

"Jacob Skaria" wrote:

Hi Trevor

1) -- converts boolean TRUE/FALSE to 1 and 0...for the sumproduct..You
can
find an explanasion here
http://mcgimpsey.com/excel/formulae/doubleneg.html
You can also use

=SUMPRODUCT((A2:A10="geography")*(B2:B10="restroom "))

2) You can reference to a cell with the query fields in C1. The array
rows
needs to be same for Col A and Col B...So you can have a list of
geographies
and beside you can place the formula and copy down as required...

=SUMPRODUCT(($A$2:$A$100=C1)*($B$2:$B$100="restroo m"))

If this post helps click Yes
---------------
Jacob Skaria


"trevor" wrote:

Jacob,
A couple of questions:
1) what does the "--" do? I've never used them before in a function
2) if I want to reference a cell with "Atlanta" in the Geography column
instead of typing "Atlanta" does the formula change?
thanks a ton

"Jacob Skaria" wrote:

Try
=SUMPRODUCT(--(A2:A10="geography"),--(B2:B10="restroom"))

If this post helps click Yes
---------------
Jacob Skaria


"trevor" wrote:

I have an array of stores in various geographies and am trying to
count the
number of stores in a particular "Market" that also have
"Restrooms". I'm
essentially trying to get a count of how many stores in each
"market" have
"restrooms". For instance, I have 250 stores, 5 of which are in
Atlanta and
among those 5, only 2 have Restrooms. I'd like to use this
function for
every geography so that next to my 20 geography's I can include a
column that
shows how many "restrooms" in each geography.. Any help would be
greatly
appreciated.

Using "COUNTIF" and "AND" functions together
 

Hi,

You may also use a pivot table. Drag the stores column to the row area,
drag the restrooms column to the data area. If you get the sum instead of
the count, then right cluck, select value field settings and select count

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"trevor" wrote in message
...
I have an array of stores in various geographies and am trying to count
the
number of stores in a particular "Market" that also have "Restrooms". I'm
essentially trying to get a count of how many stores in each "market" have
"restrooms". For instance, I have 250 stores, 5 of which are in Atlanta
and
among those 5, only 2 have Restrooms. I'd like to use this function for
every geography so that next to my 20 geography's I can include a column
that
shows how many "restrooms" in each geography.. Any help would be greatly
appreciated.