=SUMIF for calculating budget on multiple rows.

kwestmark

Hi! I have a sheet that breaks down different accounts. Some line items within an account will have a cost associated with it. I want to be able to calculate the cost of those line items per account. If I do a simple =SUM formula, it gets confused as things are added, changed, filtered etc.

I tried to write a =SUMIF and it worked but only for the first parent/child account. The formula is =SUMIF(Account:Account, "Acct 1", Budget:Budget). But when I go to write the formula for the next Acct. Acct 1 becomes blocked and Acct 2 becomes a circular reference.

I don't want to set a row range because it's constantly changing. Any ideas?

Paul Newcome

Try:

=SUM(DESCENDANTS())

kwestmark

Thanks Paul. I tried that but it calculates the entire row. So in this case it calculates everything in both Acct 1 and Acct 2. :(

Paul Newcome

It shouldn't because the Acct 2 rows are not descendants of the main Acct 1 row. Where exactly are you putting this formula?

kwestmark

I copy and pasted your formula: =SUM(DESCENDANTS()) do I need to add something specific to my sheet to complete it?

Paul Newcome

Where exactly are you putting the formula?

Do you have a filter applied?

kwestmark

In the budget column in the blue box/header. It's plugged in on the above screen shot where you see $1700 but you can see that is calculating everything for Acct 1 and Acct 2 and not just Acct1.

There are some filters to remove completed line items but that needs to remain.

Paul Newcome

The formula is also picking up on the hidden rows. If you need to adjust the output of the formula then you would need to change it to a SUMIFS and replicate the filter range/criteria sets within the formula.

kwestmark

The range won't work for me on this option. What I'm hoping to do is to count the cells in the budget column that match "Acct 1" text in the Account column. I got the formula to work but it looks like it only works once and then the other sections (ie: Acct 2) breaks the formula and gets a circular reference error. So I was trying to think of other solutions.

Paul Newcome

If you are using the DESCENDANTS range then you should not be running into a circular reference issue.

SUMIFS(DESCENDANTS(), DESCENDANTS(Account@row), @cell = Account@row)

kwestmark

Fantastic! That solves it! Thanks so much, Paul. Much appreciated!

Paul Newcome

Happy to help. 👍️