Count of Cells less than a Date

Jerry in SC

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 drop-down 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.

Paul Newcome

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")

Jerry in SC

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.

Paul Newcome

Excellent. Happy to help!