Formula Help - Deals closed in 2020

Deb White

I'm working in the Summary Sheet and attempting to get a total of deals closed in 2020 for only 4 of the 16 business units I have in my sheet. I'm using this formula:

=COUNTIFS([Business Unit]:[Business Unit], @cell = "Koala", Stage:Stage, @cell = "6 - Closed", IFERROR (YEAR @cell = 2020)

This is giving me an error message, but I still need to add the other 3 business units into this equation. Not sure where I'm going wrong. Thanks for any help you can provide.

Mike Wilday

In your year formula modify it like this:

=IFERROR(YEAR([Your Column Name]@row), "")

THis will make the column blank if there is an error and stop throwing that error in your countif.

Mike Wilday

Try this revision? Why was the IFERROR in there?

=COUNTIFS([Business Unit]:[Business Unit], @cell = "Koala", Stage:Stage, @cell = "6 - Closed", YEAR(@cell) = 2020)

Deb White

Mike - tried your revision and it didn't work.

In trying to get closed deals for 1 business unit I changed my formula and I'm getting closer:

COUNTIFS([Business Unit]:[Business Unit], @cell = "Koala", Stage:Stage, @cell = "6 - Closed", [Target / Actual Close]:[Target / Actual Close], @cell, YEAR:YEAR, @cell = 2020)

I have a column in the sheet that shows what the year is pulling from the target / actual close date column.

The revised formula returns an error of invalid data type.

Mike Wilday

Could you share a screenshot of your headers?

The highlighted part below doesn't make sense and isn't a condition. What are you trying to look for in the Target/Actual close column?

Deb White

Mike - screenshot as requested:

Mike Wilday

So I would remove the Target/Actual Close query if you're already using the year. Try this:

COUNTIFS([Business Unit]:[Business Unit], @cell = "Koala", Stage:Stage, @cell = "6 - Closed", YEAR:YEAR, @cell = 2020)

This should give you everything from 2020 that has Koala, and is in stage 6-closed.

Mike Wilday

Actually you don't need @ cell at all.

COUNTIFS([Business Unit]:[Business Unit], "Koala", Stage:Stage, "6 - Closed", YEAR:YEAR, 2020)

Does this work?

Deb White

Unfortunately, still getting the message #invalid data type. I see it highlighting my columns that are in the formula so not sure why there is the issue

Mike Wilday

Make sure the column you are putting the formula in is a text/number formatted column. Right-click on the column header to edit the column type. You might be trying to add the data to a different column type.

For more on that error:

https://help.smartsheet.com/articles/2476176-formula-error-messages#toc--invalid-data-type

Deb White

I'm actually putting the formula in the Summary Sheet so I can pull to pull a metric and/or chart for my dashboard.

Mike Wilday

You can edit the field type by clicking on the three-dot menu next to your field name. It should say Text / Number. My example below shows date.

Deb White

Yes - that was one of the first things I did check when I first ran into issues - this is very frustrating. I have it working on a metric sheet but wanted to move away from the metric sheet and use this summary sheet.

Mike Wilday

Hmmm. would you be willing to share the sheet with me temporarily? mwilday@lapu.edu

Deb White

Mike - unfortunately, I cannot share the sheet - too much confidential info in there and yes, I do have missing dates in my Year column. I think that's why I had the IFERROR in my original formula

Mike Wilday

In your year formula modify it like this:

=IFERROR(YEAR([Your Column Name]@row), "")

THis will make the column blank if there is an error and stop throwing that error in your countif.

Deb White

It worked! Fixing that column allowed it to flow through - THANK YOU for your patience in working through this with me. You Rock!