Pulling data only for current year

Deb White

I have a metric sheet pulling in data from a pipeline report to show how many closed deals we have for that region from 2016 thru to today. I now need to add a formula to show only the closed deals within that region for 2020 and display that in a new column.

The formula I used to pull the entire closed deals was:

=COUNTIFS({Business Unit}, [Business Unit]1, {Stage}, Stage$9

Any help would be appreciated. Thanks!

Jenna Bailey

@Deb White, Hi Deb,

I think referencing a helper column may be the easiest way to do this, as Mark suggested.

In your source sheet create a new column, within that column type in the formula

=IFERROR(IF(AND(YEAR(TODAY()) = YEAR([Target / Actual Close]@row)), 1, 0), 0)

What this does is takes a look at your Target / Actual Close column and verifies if the year matches what the year of today's date is then kicks out the number 1 which you can change to say "Current Year", or "Yes".. I just use the 1 in this place so that I can easily reference this in a cross-sheet formula. Once you have done that in Row 1 you can right-click and choose 'Convert to Column Formula' so that all the rows will be populated appropriately.

From here you will change your formula as follows: =COUNTIFS({Business Unit}, [Business Unit]1, {Stage}, Stage$9, {Target / Actual Close}, 1)

Mark Cronk

Hi @Deb White,

Try:

=COUNTIFS({Business Unit}, [Business Unit]1, {Stage}, Stage$9, {insert close date range}, YEAR(@cell)=2020).

This just adds a 3rd criteria to your function that looks at the close date range and the year being 2020. All 3 criteria have to be met in order for it to be counted.

Work?

Mark

Deb White

Hi Mark,

Thanks for your response. Unfortunately, it did not. BUT, I'm sure it's user error. I am very new to formulas.

I amended the formula to:

=COUNTIFS({Business Unit}, [Business Unit]1, {Stage}, Stage9, {Target / Actual Close}, YEAR (@cell)=2020)

I get an unparseable error. The Target/Actual Close is the column from my pipeline that will show that date the deal was closed. It will have a data such as 08/28/2020.

Regards,

Deb

Mark Cronk

Hi @Deb White ,

OK, plan 2. Can you insert a helper column into your sheet where you can calculate the close year? The column formula would be =IFERROR(YEAR([Target / Actual Close]@row),""). Then change your COUNTIFS statement to =COUNTIFS({Business Unit}, [Business Unit]1, {Stage}, Stage9, {Close}, =2020) where {Close} is the helper column with your calculated close year.

There is almost always a way. Sometimes it takes a few tries.

Mark

Jenna Bailey

Is your date field formatted as a date column? Try using an IF statement before the countifs.

I would re-select the two fields where your criteria is ([Business Unit]1 and Stage$9) just to confirm those are mapped correctly.

=IF(AND(YEAR(TODAY()) = YEAR({Target / Actual Close})), COUNTIFS({Business Unit}, [Business Unit]1, {Stage}, Stage$9),"")

Deb White

Mark - when I added the helper row as you suggest I don't get an error message, however, nothing populates in that column. When I then adjusted my COUNTIFS formula I get an incorrect argument

Jenna, I know my criteria for business unit and stage are correct as I have a count from 2016 onwards. I tried your formula but I get an invalid data type error. I don't understand what the and year today stuff is asking.

I do appreciate both of you jumping in on this.

Jenna Bailey

@Deb White, Hi Deb,

I think referencing a helper column may be the easiest way to do this, as Mark suggested.

In your source sheet create a new column, within that column type in the formula

=IFERROR(IF(AND(YEAR(TODAY()) = YEAR([Target / Actual Close]@row)), 1, 0), 0)

What this does is takes a look at your Target / Actual Close column and verifies if the year matches what the year of today's date is then kicks out the number 1 which you can change to say "Current Year", or "Yes".. I just use the 1 in this place so that I can easily reference this in a cross-sheet formula. Once you have done that in Row 1 you can right-click and choose 'Convert to Column Formula' so that all the rows will be populated appropriately.

From here you will change your formula as follows: =COUNTIFS({Business Unit}, [Business Unit]1, {Stage}, Stage$9, {Target / Actual Close}, 1)

Deb White

Jenna/Mark - thank you both! The Helper column helped and Jenna appreciated you explaining what the formula is actually doing so I can learn!