Count of Cells less than a Date
Hello,
I am working on a COUNTIF Formula which is not working.
The whole formula which works:
=SUMIF(Workstream:Workstream, ="Asset Mgmnt", [HIDE ME % COMP]:[HIDE ME % COMP]) / COUNTIF(Workstream:Workstream, ="Asset Mgmnt")
Where Workstream is a dropdown column, and Hide Me % Comp is a % column.
Focusing on the second side of the formula (doing a division to get an average), I updated the formula as follows, only to receive Unparseable:
=SUMIF(Workstream:Workstream, ="Asset Mgmnt", [HIDE ME % COMP]:[HIDE ME % COMP]) / COUNTIFS([End Date]:[End Date], < DATE(2020, 1, 1), (Workstream:Workstream, ="Asset Mgmnt")
I also tried, with the same result:
=SUMIF(Workstream:Workstream, ="Asset Mgmnt", [HIDE ME % COMP]:[HIDE ME % COMP]) / COUNTIFS([End Date]:[End Date], YEAR(@cell) = 2019, (Workstream:Workstream, ="Asset Mgmnt")
I also tried, with an Invalid Operation result:
=SUMIFS([End Date]:[End Date], YEAR(@cell) = 2019, [HIDE ME % COMP]:[HIDE ME % COMP], Workstream:Workstream, ="Asset Mgmnt") / COUNTIF(Workstream:Workstream, ="Asset Mgmnt")
On the same sheet I used the following formula with success, but gives me a sum (and does not compare the Workstream column):
=SUMIF([End Date]:[End Date], YEAR(@cell) = 2019, [HIDE ME % COMP]:[HIDE ME % COMP])
Thoughts?
Thanks.
Comments

Use your first formula and remove the open parenthesis before your workstream range within the COUNTIFS portion.
=SUMIF(Workstream:Workstream, ="Asset Mgmnt", [HIDE ME % COMP]:[HIDE ME % COMP]) / COUNTIFS([End Date]:[End Date], < DATE(2020, 1, 1), Workstream:Workstream, ="Asset Mgmnt")

Paul,
Thank you for the extra set of eyes. I usually count open / close ( ), but I guess I miscounted again. Once removed, the formula worked perfectly.
Thanks again.
Jerry S.

Help Article Resources
Categories
Check out the Formula Handbook template!