Summing children rows without double counting parents

Hello friends-

I am creating a spreadsheet to help track project financials for a specific grant that the non-profit which I work for received.

Specifically in the Summary Table, I have a sumifs formula which grabs specific line items based on the date and which installment of the grant they are using. I believe that the error is being caused by the fact that it is trying to double sum the parent and the children rows.

Formula in [Date]3 (which is dragged down and across for the rest of the years and installments):

=SUMIFS($[Amount (USD)]$20:$[Amount (USD)]$158, $[Hidden: Year]$20:$[Hidden: Year]$158, YEAR(@cell) = YEAR($[Hidden: Year]3), $[Hidden: Installment]$20:$[Hidden: Installment]$158, =Date$1)

If anyone sees any clarity it this, I would be super appreciative.

smartsheet question.PNG

Find more posts tagged with

Non-profit

Sheets

Finance

Project Management

Comments

Shaine Greenwood

Hello,

You'll get the #INVALID DATA TYPE error when your formula is given data that it's not designed to work with. (More on this and all of our function errors here.)

Is it possible that you're referencing values that aren't actually dates (in a date column) and trying to compare them to date values?

One small note on the syntax of your function is that you can take the equal sign off before Date$1 at the end.

One quick way to troubleshoot this is to create a SUMIF formula that references the individual criterion one by one. Whichever criterion returns that error, you'll want to see how the column itself is formatted.

Another trick is to add the DATE function to your formula to convert the free text value into a date value. More on this here: https://help.smartsheet.com/function/date

Paul Newcome

you're referencing values that aren't actually dates (in a date column) and trying to compare them to date values

This was the only way I could replicate that specific error. I had manually entered dates into a text/number column in my sheet that was the equivalent of the =Date$1 portion of your formula.

Sign in to join the conversation!

Quick Links

Trending discussions

Automation not working
I have a Smartsheet automation setup to reminder the planning to complete their sections. It is setup to remind weekly until completed box is checked. They received 2 reminders and then the reminders stopped. Any idea what could be wrong? I can't tell that anything has changed in the sheet. Thanks Freda
How to disable popups that obscure my formula?
I'm editing formulas in a Sheet Summary. However, I'm prevented from seeing the formula by an annoying "helpful" popup that obscures my view. I can't figure out how to get rid of the popup. Does anyone know?
Workflow Error: "Recipients: Edit workflow to fix recipients list"
I am trying to create a workflow and for some reason it gives me an error with the email I entered as the recipient for my Alert. It is an email that I know works, it is shared to the sheet, and the access to the sheet is unrestricted. I am perplexed at what the issue is. Anyone else get this and know what the issue is?