SUMIF Function- exclude what has been completed

Mina S

Hi, I am trying to create a formula that shows how many pieces of art (POA) have not been completed in a given month and that have been completed in a given month.

Here is my table below. I am currently using this formula to sum how many POAs would be completed in a given month.

FORMULA: SUMIFS({High LevelArtwork Status: POA Count}, {High Level Artwork Status: FK DATE}, IFERROR(MONTH(@cell), 0) = [Primary Column]@row, {High Level Artwork Status: FK DATE}, IFERROR(YEAR(@cell), 0) = 2023)

But what I really need to do is once adate in January has passed, to have the cell not calculate that piece of art as part of the total anymore. Separately, I need to create another column that does calculate only what has been completed that month by only summing dates in the past for that month and year.

How would I change this formula in order to do that? Thank you!

Paul Newcome

Are you able to provide a screenshot of the source data?

Mina S

Hi thanks for your help. Here is a screenshot of the reference file.

Paul Newcome

Your formula should only be summing rows where the date is in that particular month/year. Is the FK DATE not the "Actual"date?

Mina S

HI Paul, sorry I don't understand your question. The Full Kit date is the actual date that I need.

Paul Newcome

In that case I am not sure exactly what you are trying to do then.

The formula in your original post should only be pulling in rows where the Full Kit Date is within that month/year and not rows that aren't.

Mina S

Hi Paul, yes that is correct. I only want it to pull in the month/year but the current formula pulls the month/year for all dates regardless of if it is a date in the past. I want to reformat with two different formulas. One formula that only sums the number of POAs if the dates are in the future. And one formula that only sums the number of POAs if the dates are in the past. Does that make sense?