Current Month Formula

Options
✭✭✭✭
edited 03/29/23

I keep getting an error on the below formula trying to COUNT for the previous month. I am reporting from two sheets, the live one and an archived version. This formula works fine until I add the current month requirement, then it throws up an error for #INCORRECT ARGUMENT:

=COUNTIFS({Sales Pipeline Opportunity}, (CONTAINS("Enquiry raised", @cell)), {Archive Sales Start}, AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))) + COUNTIFS({Archive Sales Opportunity}, (CONTAINS("Enquiry raised", @cell)), {Archive Sales Start}, AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))

Appreciate the help.

• ✭✭✭✭
Options

I realised I did not need to pick up the enquiry row, so formula now works:

=SUMIFS({Sales Pipeline Amount}, {Sales Pipeline Start}, AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))) + SUMIFS({Archive Sales Amount}, {Archive Sales Start}, AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))

• ✭✭✭✭
Options

I realised I did not need to pick up the enquiry row, so formula now works:

=SUMIFS({Sales Pipeline Amount}, {Sales Pipeline Start}, AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))) + SUMIFS({Archive Sales Amount}, {Archive Sales Start}, AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))

• Employee
Options

Glad to hear that it works now! 🙂