Count # of Parent Rows

TPALJA

Just looking for a the formula to count the parent rows from the primary column. I dont need the children in this case.

Humashankar

Hi @TPALJA - Happy Friday and March 1st

It is motivating the tips helped you to move on with your use case.

Please let me know how you completed the problem statement - Happy to help further !!

Kindly mark this as solved use case if in case it is all aligned - which will also help other pursures on this topic !!

Warm Regards!!

Humashankar

Hi @TPALJA

It is doable with the help of applying helper column

-> Insert a new column next to your primary column as Helpercolumn

-> In the first cell of the helper column, use a formula to identify parent rows. You can use a formula that checks if the parent row contains data in the primary column and returns a value

-> use a formula that checks if the parent row contains data in the primary column and returns a value 

=IF(ISBLANK([Primary Column]@row), "", 1)

-> Replace [Primary Column] with the reference to your primary column

-> Copy the formula down to apply it to all rows in the helper column. This will mark each parent row with a value of 1

-> Use the COUNTIF function to count the number of parent rows in the helper column as shown below

=COUNTIF([Helper Column]:[Helper Column], 1)

-> [Helper Column] with the reference to your helper column

So out of this exeution indirectly it will count the number of parent rows in your use case by using a helper column to identify them based on the presence of data in the primary column

Hope this helps - Happy to help further !!

Thank you very much and have a fantastic day!

Warm regards

Humashankar

We can also do this use by adding checkbox column - Let me know if you need additional assistance we can solve

TPALJA

@Humashankar I had forgotten all about doing the checkbox so thanks for the helpful reminder

Humashankar

Hi @TPALJA - Happy Friday and March 1st

It is motivating the tips helped you to move on with your use case.

Please let me know how you completed the problem statement - Happy to help further !!

Kindly mark this as solved use case if in case it is all aligned - which will also help other pursures on this topic !!

Warm Regards!!

TPALJA

@Humashankar follow up question. I want to know the number of parent rows, that were paid and not paid for the date of 2023, 11, 29. This formula works but I also want to add the paid date of 2023, 11, 29.

=COUNTIFS([Column24]:[Column24], 1, [Paid Date ]:[Paid Date ], "Not Paid", [Not paid date ]:[Not paid date ], DATE(2023, 11, 29))

Humashankar

Hi @TPALJA - Great update,

Try with the below one and see you able to solve - if not, let me know:

Lets make an array formula with logical conditions - This is one way 

Lets use the functions like IF and AND along with array operations to count the number of parent rows that meet the criteria - Paid Rows with Paid Date 2023-11-29

=SUM((PaidColumnRange="Yes")*(PaidDateColumnRange=DATE(2023,11,29)))

Above code counts the number of parent rows where the Paid column is "Yes" and the Paid Date column matches 2023-11-29

Lets count Unpaid Rows with Paid Date 2023-11-29

=SUM((PaidColumnRange="No")*(PaidDateColumnRange=DATE(2023,11,29)))

Above one covers - the number of parent rows where the Paid column is "No" and the Paid Date column matches 2023-11-29

Both formulas use array operations where (PaidColumnRange="Yes") and (PaidDateColumnRange=DATE(2023,11,29)) evaluate to arrays of TRUE/FALSE values based on the conditions. 

Multiplying these arrays together results in a new array where TRUE values are treated as 1 and FALSE values as 0. 

The SUM function then calculates the total count by summing up the elements of the resulting array

Humashankar

One other option would be -

Try using this or you can prefer with Count IF Function:

For this - Ensure that the PaidColumnRange and PaidDateColumnRange with the actual ranges of your Paid and Paid Date columns based on your table info

To count the number of parent rows that were paid and not paid for the date 2023-11-29, 

along with including the paid date itself, use the COUNTIFS function with two criteria: 

one for checking if the row is paid ("Yes" in the Paid column) and another for checking if the paid date matches "2023-11-29" in the Paid Date column

the Code to include the count of parent rows with the paid date of 2023-11-29

=COUNTIFS(PaidColumnRange, "Yes", PaidDateColumnRange, DATE(2023,11,29))

The above one solves - number of parent rows that were paid ("Yes" in the Paid column) and had the paid date as 2023-11-29 in the Paid Date column. 

The DATE function is used to specify the date as 2023-11-29

Same sort this time - Lets count the number of parent rows that were not paid for the same date

=COUNTIFS(PaidColumnRange, "No", PaidDateColumnRange, DATE(2023,11,29))

This time it counts the number of parent rows that were not paid ("No" in the Paid column) and had the paid date as 2023-11-29 in the Paid Date column