Can someone please help me with SUMIF formula?
I am trying to use a formula that will add value in children cells when cells do not include an asterisk in front of the value. Basically, I am creating an invoice tracking spreadsheet and I want to be able to input expected monthly invoices (based on the contract) on the front end, but not have those values added to the total amount in parent cell until a new amount is input without an asterisk (actual amount invoiced). I would also like to be able to automatically show the difference from the expected --> actual for each cell, but I am not sure that is possible. I appreciate any help!
Answers
-
Are you able to provide screenshots for context?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Yes, so right now I have the parent values for each month automatically adding the children values.
but I really want to be able to go ahead and fill out this spreadsheet with expected invoice amounts for each vendor/month. EX: Vendor ABC has a contract for 60k and is expected to bill 12k/month for 5 months of 2024. So if I input *12,000 for Jan,Feb,April,June,Oct., I dont want those amounts to be included in parent rows because they are only anticipated amounts. When the invoices are received, I will go in and replace the *12,000 with let's say, 11,550.... and since this is the actual amount, I want it included in the total in the parent row. Ideally, I would like to also see the difference (expected-actual) populate in red below the new amount.
-
The first bit is straightforward enough. Using the SUM(CHILDREN()) should continue to work because the asterisk turns it into a text string which can't be added anyway.
I'm not sure I follow that last bit though about the difference populated in red below the new amount. Can you provide a screenshot of a manual mock-up so that I can see what you are wanting to automate?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Paul, thank you very much for your help... I understand what you are saying about the asterisk turning it into a text string so it cant be added anyways.. but I still want the expected amounts to be shown. This is what I see right now...... and it shows expected payments but the parent row populates totals. I dont want the parent row to populate totals.
If I add an asterisk in front of the contract amounts, 13K for example, it puts #invalidoperation in the following cells because I have a formula in those cells to take the total and divide into equal payments. I want those expected payments to show still. How can I put the expected amounts in and not add to the parent row? When I go back in and add actual amounts at a later time, I then want the actual amounts to be calculated.
-
I'm not sure I follow. Can you provide a screenshot of what's going on with the asterisks?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!