Complex Formula COUNTIFS for Last Month

SueinSpain

I have 2 working formulas that I wish to combine.

The first is a count where MSD Data field = Yes - it works and gives me a result of 65

=COUNTIFS([MSD Product]:[MSD Product], ="Yes")

The second is a complex formula with dates (that this community helped me construct genius that they are) which gives me a count in the previous calendar month i.e January - you can see why I needed help !!!

=COUNTIFS([Raised on]:[Raised on], AND(IFERROR(MONTH(@cell), 0) = MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1), IFERROR(YEAR(@cell), 0) = YEAR(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1)))

What I need to achieve, and cannot seem to get to work, is where the MSD Data field = Yes and the Raised On field was last month.

I've tried adding the MSD part to the front then a comma then the raised on etc. I have also tried adding the MSD field to the end and after another AND but doesn't work.

so for example this doesn't work and gives #unparseable error

=COUNTIFS([Raised on]:[Raised on], AND(IFERROR(MONTH(@cell), 0) = MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1), IFERROR(YEAR(@cell), 0) = YEAR(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1)) AND ([MSD Product]:[MSD Product], ="Yes"))

I have passed my ability to compute this and really hope that you can help.

Bassam Khalil

Hi @SueinSpain

Hope you are fine, please try the following formula.

=COUNTIFS([MSD Data field]:[MSD Data field], "yes", [Raised On]:[Raised On], MONTH(@cell) = (MONTH(TODAY()) - 1))

the following screenshot is from the sample sheet i create for your case

Bassam Khalil

Hi @SueinSpain

Hope you are fine, please try the following formula.

=COUNTIFS([MSD Data field]:[MSD Data field], "yes", [Raised On]:[Raised On], MONTH(@cell) = (MONTH(TODAY()) - 1))

the following screenshot is from the sample sheet i create for your case

SueinSpain

You are an absolute genius.

I have used this, adapted this and it works brilliantly thank you

For a new challenge :) I want to do something very similar with =SUMIFS but again cannot get the right number of commas to work so obviously being stupid (again!)

I am trying to add up the [Value of Goods] for last month. I have ...... but get #unparseable

=SUMIFS([Value of Goods]:[ Value of Goods], [Raised on]:[Raised on], MONTH(@cell) = (MONTH(TODAY()) - 1))

Just cannot see where I am going wrong so hope you can help with this one too.

Andrée Starå

Hi @SueinSpain

Try something like this.

=SUMIFS([Value of Goods]:[ Value of Goods], [Raised on]:[Raised on], MONTH(@cell) = MONTH(TODAY()) - 1)

This was wrong in yours.

=SUMIFS([Value of Goods]:[ Value of Goods], [Raised on]:[Raised on], MONTH(@cell) = (MONTH(TODAY()) - 1))

Did that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

SueinSpain

no still #unparseable

It is in a summary sheet if that makes any difference?

Andrée Starå

@SueinSpain

Do you mean Sheet Summary and not another sheet?

SueinSpain

I think the rain is getting to me - yes sorry in the Sheet Summary for a single smartsheet

I previously used your brilliant =COUNTIFS to get me total number of records for previous month (3 months & 12 months - you are a genius)

=COUNTIFS([Raised on]:[Raised on], AND(IFERROR(MONTH(@cell), 0) = MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1), IFERROR(YEAR(@cell), 0) = YEAR(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1)))

Then needed to epand to count if a value in a field is met and record in the last month - got there with help again this time from Bassam!

=COUNTIFS([MSD Product]:[MSD Product], "Yes", [Raised on]:[Raised on], AND(IFERROR(MONTH(@cell), 0) = MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1), IFERROR(YEAR(@cell), 0) = YEAR(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1)))

But now instead of a count I need the sum of a [Value of Goods] field.

Can do the grand total so all field contents OK. just have troubles doing it for the last calendar month. Thinking of deleting users :) so they don't keep asking for these difficult solutions.

Andrée Starå

@SueinSpain

Haha! No worries!

Thanks for the kind words.

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

SueinSpain

have shared on the sheet - the correct one 2nd time around :)