Sumif with a date range
I am trying to calculate the billing amount for an individual month when there are multiple months in the column. Below will calculate all of the billing, but I want to call out 11/2020, 12/2020, 1/2021. What can I add?
=SUMIF({Monthly Detail Range 1}, [Primary Column]@row, {Monthly Detail Range 2})
Best Answer
-
In that case you will need to add an additional range.
=SUMIFS({Column to Sum}, {Role Column}, [Primary Column]@row, {Date Column}, AND(IFERROR(YEAR(@cell), 0) = 2020, IFERROR(MONTH(@cell), 0) = 11))
Answers
-
You can replace the year and month text in the below with cell references or another year and month function if needed, but here is the general idea...
=SUMIFS({Column to Sum}, {Date Column}, AND(IFERROR(YEAR(@cell), 0) = 2020, IFERROR(MONTH(@cell), 0) = 11))
-
=SUMIFS({Monthly Detail Range 2}, [Primary Column]@row, {Monthly Detail Range 3}, AND(IFERROR(YEAR(@cell), 0) = 2020, IFERROR(MONTH(@cell), 0) = 11))
Is coming back as an error #INCORRECT ARGUMENT SET
Thoughts?
-
That is because of you [Primary Column]@row reference. What is that referring to?
-
I want to separate by roles
-
In that case you will need to add an additional range.
=SUMIFS({Column to Sum}, {Role Column}, [Primary Column]@row, {Date Column}, AND(IFERROR(YEAR(@cell), 0) = 2020, IFERROR(MONTH(@cell), 0) = 11))