# 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

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭✭

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.

• ✭✭✭✭✭

You are a lifesaver! Thank you that worked.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!