Count Column A based on Column B
Hi. I have one column "N/R" which has a type of store opening New or Relocation. I then have another column with the date that the store is opening (this is a date column). I am looking for a formula that would count how many new stores or how many relocation stores are opening in the current month. I could probably write several and break them up over the months but I am looking for something that will update on it's own as I want to use this on a dashboard.
TIA
Best Answer
-
Add an AND to the COUNTIFS criteria. Set the year value to the years you want to include. The example is set to 2020:
=COUNTIFS([N/R]:[N/R], = "Net New", [Store Opens]:[Store Opens], AND(MONTH(@cell) = MONTH(TODAY()), YEAR(@Cell)=2020))
Work?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Answers
-
Try:
= Counifs([N/R]:[N/R], OR(@cell="R", @cell="N"), [opening date]:[opening date], month(@cell)=month(today())
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
I attempted this and altered it a bit so I could get the individual counts, the only issue is it gives me the wrong information. To test I used the current month we have a total of 17 locations opening, 5 new locations, 11 relos, and 1 that we don't consider anything. I get the 11 relocations when I use the formula but I get 8 new locations
For New:
=COUNTIFS([N/R]:[N/R], OR(@cell = "Net New"), [Store Opens]:[Store Opens], MONTH(@cell) = MONTH(TODAY()))
For Relocations:
=COUNTIFS([N/R]:[N/R], OR(@cell = "Relocation"), [Store Opens]:[Store Opens], MONTH(@cell) = MONTH(TODAY()))
-
Hi @Christopher Pretty ,
Hmmm...Try:
=COUNTIFS([N/R]:[N/R], = "Net New", [Store Opens]:[Store Opens], MONTH(@cell) = MONTH(TODAY()))
You don't need the OR for 1 criterion.
Confirm that all the news are listed as "Net New". Convert to a drop down and limit use to the drop down if you're not already. Good luck.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
I figured out the issue, we have stores opening next year also on the sheet. Is there a way to put a year statement on this also? I tried just duplicating the month statement and using AND but I get an error.
-
Add an AND to the COUNTIFS criteria. Set the year value to the years you want to include. The example is set to 2020:
=COUNTIFS([N/R]:[N/R], = "Net New", [Store Opens]:[Store Opens], AND(MONTH(@cell) = MONTH(TODAY()), YEAR(@Cell)=2020))
Work?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
You are a lifesaver! Thank you that worked.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 419 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 142 Just for fun
- 58 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
