Count Column A based on Column B

Christopher Pretty

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

Mark Cronk

@Christopher Pretty ,

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

Mark Cronk

Hi @Christopher Pretty,

Try:

= Counifs([N/R]:[N/R], OR(@cell="R", @cell="N"), [opening date]:[opening date], month(@cell)=month(today())

Mark

Christopher Pretty

https://community.smartsheet.com/discussion/comment/269907#Comment_269907

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()))

Mark Cronk

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

Christopher Pretty

https://community.smartsheet.com/discussion/comment/269936#Comment_269936

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.

Mark Cronk

@Christopher Pretty ,

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

Christopher Pretty

https://community.smartsheet.com/discussion/comment/270031#Comment_270031

You are a lifesaver! Thank you that worked.