COUNTIFS With Date Within This Month

Derek Ash

Trying to put together a COUNTIFS formula that counts the number of times a certain number is listed in a column(Store Number) of a sheet(Used Electric Guitars Magento(Use This)) only when the value listed in the date column is within the current month

Thanks in advance for any help you can provide

Kelly Moore

Hey @Derek Ash

Are you looking for a formula within the same source sheet of data, or is the data Used Electric Guitars Magento(Use This) a different sheet? Not knowing your data, I'll assume you are looking cross sheets and the store number is in both your target sheet and your Used Electric Guitars sheet. We can tweak the formula if my assumptions are incorrect.

=COUNTIFS({Used Electric Guitars Magento(Use This) Store Number}, [target sheet Store Number column], {Used Electric Guitars Magento(Use This) Date column}, IFERROR(MONTH(@cell),0)=MONTH(TODAY()))

Assuming this is cross reference sheets, remember that you cannot copy paste a cross-reference formula into your sheet. You must click the insert reference link within the formula window and build each cross reference yourself. If this is cross reference and you need refreshing on how to build one, I'll be happy to help.

When using the TODAY() function, you could consider adding automation to the same sheet that contains the formula above to keep your TODAY() function always synced with the current date. For this you'll need a helper column formated as a DATE column. I usually call this helper 'TODAY() helper'. In this column, in a single row, insert a date that is in the past. Since today is April 18, 2021 any day prior to that will work. This date will not affect any other data in your target sheet and you can hide this column once you insert the date. Save the sheet. Go the automation menu and build an automation routine using the screenshot below as guide. Be sure to set up the recurrence to run daily.

cheers,

Kelly

Derek Ash

Hello Kelly

Thanks for the reply. For the formula I am trying to create, all the data(Store Number Column & Date Column) is on the Sheet I am trying to reference(Used Electric Guitars Magento(Use This) )

The number should only be counted if the Store Number Listed in Column A of the sheet with the formula(Category Breakdown Per Store(This Month))equals the Store Number listed on the reference sheet(Used Electric Guitars Magento(Use This) )and the Date Column listed on the reference sheet(Used Electric Guitars Magento(Use This) )is within the current month

Let me know if that information changes the formula

I also went ahead and set up the automation you suggested on the sheet with the formula in the event that is necessary

Let me know your thoughts

Thanks

Derek Ash

Kelly Moore

Hey Derek

So there are two sheets involved- the sheet you're referencing with the data and the destination sheet where the formula resides? My original formula should cover that- although I did forget to type in an @row .

=COUNTIFS({Used Electric Guitars Magento(Use This) Store Number}, [destination sheet Store Number column]@row, {Used Electric Guitars Magento(Use This) Date column}, IFERROR(MONTH(@cell),0)=MONTH(TODAY()))

The COUNTIFS function uses the syntax =COUNTIFS(range1, criteria1, range2, criteria2, range3, criteria3, and so on). The formula above says to Count IF the cells in 'Used Electric Guitars Magento(Use This) Store Number' column as the range that are equal to the destination sheet store number column (I think you called this ColumnA) AND where 'Used Electric Guitars Magento(Use This) Date column' cells that have the same month number as TODAY().

For more information on cross sheet references please see this link. https://youtu.be/u01GHwmlDRA

You must build the cross sheet references. You cannot simply copy paste this formula in. You will change the name of [destination sheet Store Number column] to match your column name. Shout out to me if you need help with anything I just suggested.

Kelly

Paul Newcome

One additional suggestion just in case you could have multiple years listed on the same sheet is to also incorporate a YEAR criteria to filter for current month AND current year.

=COUNTIFS({Used Electric Guitars Magento(Use This) Store Number}, [destination sheet Store Number column]@row, {Used Electric Guitars Magento(Use This) Date column}, AND(IFERROR(MONTH(@cell),0)=MONTH(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))

Derek Ash

Hello

Thank you for your assistance on this formula. I am attempting to add another criteria to this COUNTIFS formula. The criteria would be looking at the same reference sheet. Criteria would be to COUNTIF where the column Sku Type=USED

Let me know if you have any questions and thanks for your assistance

Best Regards

Derek Ash

Sam Ash Music

Derek Ash

I am also looking to create a similar report that looks at the current week(Sunday to Monday) as opposed to the current month. I did try replacing the MONTH with WEEK but I am getting an unparseable error

Let me know your thoughts

Thanks

Derek Ash