Can someone please help me with SUMIF formula?

Courtney Clark

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!

Paul Newcome

Are you able to provide screenshots for context?

Courtney Clark

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.

Paul Newcome

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?

Courtney Clark

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.

Paul Newcome

I'm not sure I follow. Can you provide a screenshot of what's going on with the asterisks?