SUMIF formula issues

I am trying to write my 1st SUMIF formula based on a suggestion from a colleague but not coming right :(

We are setting up a budget tracking sheet for the organisation to use as many people will be adding rows to various categories I am trying to write a formula that will check the Category in the "Activity" column and then based on that SUM the values so I will always have the total irrespective of the # of rows.

These are the formulas I tried but they are not working.

I also then wondered whether I should be doing an IF and then SUM formula but had no joy with that either.

Formula attempts:

=SUMIFS([Invoice Value]:[Invoice Value], Activity:Activity, "Gifting")

=SUMIFS([Invoice Value]:[Invoice Value], (Activity:Activity, "Gifting")

=SUMIF([Invoice Value]:[Invoice Value], (Activity:Activity), "storage")

=IF(Activity:Activity, "Storage" SUM([Invoice Value]:[Invoice Value]

=SUMIF(Activity:Activity, "Storage", [Invoice Value]:[Invoice Value])

I am also going to need a formula that adds based on activity group.

If someone can put me out of my misery, hoping it is not too difficult to get this right.

Find more posts tagged with

Formulas

Accepted answers

All comments

jmyzk_cloudsmart_jp

Hi @vanstadenster

Thank you for sharing the information.

Based on my solution file (attached) and your attempted formulas, I have interpreted your data structure as one single sheet, not two separate sheets.

It seems that the sheet was visually divided into two parts in the screenshots (perhaps left and right side), but in fact, all the key data—such as Invoice Number, Business Unit, Activity Group, Activity, and Invoice Value—belongs to the same sheet.

https://app.smartsheet.com/b/publish?EQBCT=4b7dcc1b171740ad8a833b25192768a3

In the solution, I used the following formulas:

[Is Parent] =COUNT(CHILDREN()) > 0
[No Parent #] =IF([Is Parent]@row, "", MATCH(Row@row, Row:Row))
[Invoice Number] =IF(AND(ISNUMBER([No Parent #]@row), ISNUMBER([Invoice Value]@row)), 1000 + RANKEQ([No Parent #]@row, [No Parent #]:[No Parent #], 1))

[Sum of Value] =IF([Is Parent]@row, SUM(CHILDREN([Invoice Value]@row)), [Invoice Value]@row)

I hope this setup closely matches the structure you intended.

vanstadenster

Hi @jmyzk_cloudsmart_jp

Apologies, yes it is one sheet - here is the link

Thanks so much, still trying to wrap my head around it.

I have changed the Sum of Value name to approved budget as that is a fixed budget assigned given to each activity. I locked that column now.

Are you saying that I need to add helper columns to see if it is a Parent or a child and that I am then going to need to add an additional column to sum the invoice value of all the children with a certain activity name?

The dynamic field will be the Invoices that get added based on an Activity. I want to sum the "invoice value" based on the "activity"

jmyzk_cloudsmart_jp

Hi @vanstadenster

Thank you for the clarification and the link!

Unfortunately, I am unable to access the sheet. Could you please adjust the publish settings to allow access?
As I observed your sheet, it looks like you are organizing the data by grouping rows under each Activity as parent/child relationships. That’s why I initially suggested using SUM(CHILDREN()) at the parent rows to automatically sum the invoice values of the related children.

However, if you are not intending to use parent/child structure actively, another option would be to create a separate summary sheet listing unique Activities, and then use cross-sheet formulas like SUMIF or SUMIFS to sum the invoice values based on the Activity names dynamically.

Let me know if you'd like me to assist further depending on the approach you prefer!

vanstadenster

@jmyzk_cloudsmart_llc apologies here you go

Would you mind taking a final look.

If I take approach two I do a summary sheet count and then create a report?

I am not sure I can get away from parents and children. Idea is to use dynamic view for team members to input invoices against activities.

Sorry, I thought this would be super easy, looks like I got myself into a pickle on this one 😅

I tried this formula in the sheet but it gives me a circular reference:

=IF([Parent Row]@row, SUM(CHILDREN([Invoice Value]@row)), [Invoice Value]@row)

jmyzk_cloudsmart_jp

Hi @vanstadenster

In approach two, you list the activities first or, as in my example, populate enough index numbers (rows) first.

You can get the activies dynamically with this formula;

=IFERROR(INDEX(DISTINCT({invoice_sheet_updated : Activity}), Row@row), "")

Here, the ranges, like Activity, are from the sheet where the invoices are.

https://app.smartsheet.com/b/publish?EQBCT=94bfae0b79fa46618ba403d8b3404360

Then, you can get the sum or total of the invoices by Activity using this formula.

=IF(ISTEXT(Activity@row), SUMIFS({invoice_sheet_updated : Invoice Value}, {invoice_sheet_updated : Activity}, Activity@row))

The if part is to hide 0 in rows where there are no activities.

As for the circular reference, your formula uses [Invoice Value]:[Invoice Value], which references the whole [Invoice Value] column's rows, causing the circular reference, because the reference includes the cell where the formula is.

SUM(CHILDREN([Invoice Value]:[Invoice Value]))

So, instead, just use CHILDREN(), which means the children rows' cells of the cell.

https://app.smartsheet.com/b/publish?EQBCT=caac4fa5bdd94eec82d3410b986fe04b

vanstadenster

@jmyzk_cloudsmart_jp

Thanks the sum of children won't work as I need to sum it based on the activity type as well. Which is why I thought I could check activity and then sum value based on that.

I am going to try the other method now. Not sure I will get that to work but will try.

vanstadenster

Thanks @jmyzk_cloudsmart_jp I managed to implement both.

Going with the 1st solution suggested and no longer checking the "activity". I got it to populate dynamically on another sheet as you proposed, I am just not sure how to then get the sum back to my original sheet and am pressed for time so will implement option 1 am MVP and then look at Option 2 once I can figure out how to keep parent, child row integrity when people add rows.

Thanks again for all your time taken to assist me with this. I really must find time to do a course on Smarthseet formulas.

jmyzk_cloudsmart_jp

Happy to help!😁
If you have any questions, feel free to @ me in this discussion.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.