Using "COUNTIF" and "AND" functions together

[trevor]

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.

 

[Jacob Skaria]

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

If this post helps click Yes

 

[trevor]

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]

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="restroom"))

If this post helps click Yes

 

[trevor]

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!

 

[T. Valko]

See this:

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

 

[Ashish Mathur]

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