Sheet Summary Count level under a particular parent Row

Vasanth Balakrishnan

Hello Experts,

i'm trying to make sheet summary for the Row under a particular parent:

i have a smart sheet that has values in a column, date in another column

1) count the row only under a "Particular Parent"

2) Count if there is any value is provided, and

3) Year is 2020

i used this formula in sheet summary =IF(COUNT([Shop Order]3:[Shop Order]44) > 0, COUNTIFS([To Test]3:[To Test]44, IFERROR(YEAR(@cell), 0) = 2020)) seems this not considering certeria 2

please advise.

Untitled.png

Sean Morgan

Hello @Vasanth Balakrishnan ,

Assuming all the DC/SPS values are children rows of HMI & Control Servers, you could use the following Formulas to calculate how many entries are for 2020 & 2021

Summary Field 1: =COUNTIFS(CHILDREN(Date1), YEAR(@cell) = 2020, CHILDREN(Task1), ISTEXT(@cell))

Summary Field 2: =COUNTIFS(CHILDREN(Date1), YEAR(@cell) = 2021, CHILDREN(Task1), ISTEXT(@cell))

Please see this performing as intended below:

The Date1 and Task1 parts of the Formula are the Parent rows of the values on Row 1.

For more on the functions used, please see the below articles:

COUNTIFS: https://help.smartsheet.com/function/countifs

CHILDREN: https://help.smartsheet.com/function/children

YEAR: https://help.smartsheet.com/function/year

Let me know if you have any questions!

Regards

Sean