Calculating a percentage based on YTD wins

Paige S.

Hi everyone -

I'm trying to add a percentage of wins to a dashboard.

Initially I thought the easiest solution would be to add a formula to the sheet summary page and i used this but have been getting back #invalid Data Type error.

=COUNTIFS([Status/Conclusion]:[Status/Conclusion], "Won", YEAR([Date Requested]:[Date Requested]), YEAR(TODAY())) / COUNTIF(YEAR([Date Requested]:[Date Requested]), YEAR(TODAY())) * 100

So my plan B was to use a countif function calculating YTD on a calculation sheet with cross references but i can't seem to get that to work either.

The data field i am trying to use is the "requested date" which is the auto data of a row submission.

Any ideas on how to write this or how to write in a YTD formula?

Darren Mullen

@Paige S.

Initial issue I see if that you can't wrap the criterion range in a function like YEAR(). See below for the proper syntax:

=COUNTIFS([Status/Conclusion]:[Status/Conclusion], "Won", [Date Requested]:[Date Requested], year(@cell) = YEAR(TODAY())) / COUNTIF([Date Requested]:[Date Requested], year(@cell) = YEAR(TODAY())) * 100

Paige S.

@Darren Mullen Still can't get that to work. Would I need a helper column to pull out the year from the date requested column? I'm trying to minimize adding extra columns as this sheet is used across company. I'm trying to use the date requested field as my criteria for the year.

Paul Newcome

Are you getting an error message? Are you able to provide a screenshot of the formula open in the sheet as if you are about to edit it?

Paige S.

@Paul Newcome

=COUNTIFS({Sales Opportunity Tracker Status/Conclusion}, "Won", {Sales Opportunity Tracker - Date Requested}, YEAR(@cell ) = YEAR(TODAY())) / COUNTIF({Sales Opportunity Tracker - Date Requested},YEAR())) * 100

Getting Unparseable with this. trying to do it in my data sheet.

Paul Newcome

You need TODAY( there in the second YEAR function. You already have the closing parenthesis in place. Just missing the function and opening parenthesis.

You have:

YEAR()))

You need

YEAR(TODAY()))

Paige S.

@Paul Newcome now i'm getting a #dividebyzero error?

=COUNTIFS({Sales Opportunity Tracker Status/Conclusion}, "Won", {Sales Opportunity Tracker - Date Requested}, YEAR(@cell ) = YEAR(TODAY())) / COUNTIF({Sales Opportunity Tracker - Date Requested}, YEAR(TODAY())) * 100

Appreciate all the help.

Paul Newcome

That means your second piece is pulling in a zero. Do you get any numbers when you use that one on its own?

Paige S.

@Paul Newcome it returns a 0 but almost all requests are 2025

Paul Newcome

How exactly is that column populated and what are the column properties? Did you double check that your range does in fact cover the entire column by clicking on the column header when creating the cross sheet reference?

Paige S.

@Paul Newcome

confirmed it is using the entire column

The column properties is the created date which is auto populated column that is generated for each row at the time of the form submission that we use for this sheet. I would think i should be able to pull a date from this format??

Paul Newcome

Apparently I needed to take a step back. Your criteria in the second COUNTIF is off a bit.

You have

COUNTIFS({Range}, YEAR(TODAY()))

You should have it more like the first COUNTIFS:

COUNTIF({Range}, YEAR(@cell) = YEAR(TODAY()))