=SUMIF for calculating budget on multiple rows.
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?
Answers

Try:
=SUM(DESCENDANTS())

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. :(

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

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

Where exactly are you putting the formula?
Do you have a filter applied?

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.

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.

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.

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)

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

Happy to help. 👍️
Help Article Resources
Categories
Check out the Formula Handbook template!