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.
Comments
-
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
-
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.
