COUNT IF formula

mmac

Hello,

I've read through a couple of different examples, but I am not having any luck. 

I am looking to have the # Assets count total displayed in the highlighted parent row/cell based on the following criteria.

If # assets cell below the parent is greater than 0 for the total count to display.

 

Mike Wilday

Can you share a screenshot? It would be extremely helpful for understanding what you mean. 

mmac

My apologies - oversight on my part.

Mike Wilday

This should do the trick for you... 

=IF(Sum(children()) = 0 , "", sum(children())

mmac

Thank you for the reply!

Unfortunately, not working as expected. Here is a video example if at all helpful. 

https://www.loom.com/share/f28346c4cc76434bb6dd6cf3a6c983e9

Row 1 is parent row -

# Assets should count ALL cells below it in the same column if greater than 0.

Rows 3 - 8 would = 1 for example under # Assets and then row 2 would count the children below it as 6 total assets.

Row 1 would count all assets of the subset of children under US and in the example would equal to 6.

 

And CA/AU would be the same concept. CA # of assets would be counted in row 9 and AU under row 16. Those total counts would roll up into the total # of assets in row 1.

 

Mike Wilday

As long as you put the formula in all the parent rows it should work properly. Please review my screenshot showing the working data... 

 

Mike Wilday

I am assuming that you are pre-populating the base rows with 1. 

If not, try this formula in every row... This will populate a 1 in every base child row and sum the counts of the immediate children if it is a parent row. 

=IF(SUM(CHILDREN()) = 0, 1, SUM(CHILDREN()))

mmac

Worked great, thank you for your support!

Mike Wilday

Awesome! I'm glad I was able to help you out!