| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Difficulty "countif"-ing number of "L1" or "L2" on an autofiltered | Excel Worksheet Functions | |||
| Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
| can I nest COUNTIF functions(C5:C8,"apples") AND (D5:D8,"green") | Excel Worksheet Functions | |||
| HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
| Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
Linear Mode
