Parent/Children Formulas with Date Ranges

Stephanie Stoewe

Hello,

Can I get some guidance on how to properly use Parent/Children formulas for a Summary sheet that is fed by information from a reference sheet?

I have several count ifs, and sum ifs all corresponding to the month and the year.  The parents and children will all have the same metrics and hierarchy for multiple suppliers.  

The first formula is a COUNTIF, the supplier name matches the Parent, and the row was created in month 1 of year 19.  

The second formula is SUMIF, the supplier name matches the Parent, and the rows were created in month 1 of year 19 ... etc.

Any advice would be most appreciated. 

 

Shaine Greenwood

Hi—

You might try a formula like the following: 

=COUNTIFS({supplier reference},"Supplier 1", {date reference}, >= DATE(2019, 1, 1), {date reference},<= DATE(2019, 1, 31))

The above example will count anything where "Supplier 1" submitted something between 1/1/2019 and 1/31/2019.

To unpack this example formula a bit:

• Since we need to compare multiple values, this is a COUNTIFS formula: https://help.smartsheet.com/function/countifs
• This example formula uses cross-sheet references. More information on how to create them can be found in our help center: https://help.smartsheet.com/articles/2476606-formulas-reference-data-from-other-sheets
• This also uses the DATE function to compare against your system date column: https://help.smartsheet.com/function/date
• You can adapt this to your SUMIFS formula when you need to reference your numeric values: https://help.smartsheet.com/function/sumifs