Need Help with SUMIFS

DianeNelligan24_

I am trying to track my grants by Project, Award Type, and Phase. I am placing this formula in my metric sheet and referencing my project fund tracker sheet. Here is my formula:

=SUMIFS({SSq Fund Tracker | Award}, {SSq Fund Tracker | Award Type}, "Grant", {SSq Fund Tracker | Phase}, "Phase II")

In this case, I'm lookin for SSq Project Funding: sum of award amounts for Grants in Phase II.

I am getting an error message back saying 'incorrect argument'. I've double checked this formula with Smartsheet guidelines in the Help text as well as online videos. It's seems correct. I've even done each one separately and they work, but it somehow doesn't like when I put them together. Yes, there is Grant funding in Phase II. Can someone help me?

Samuel Mueller

Just want to confirm that all three of your cross sheet references are on the same sheet, and all are the entire column, correct?

I wonder if one of the cross sheet references got messed up.

DianeNelligan24_

Yes, they are all in the same sheet: SSq Fund Tracker; and yes, the reference is the entire column. I've reset them and done the calc numerous times, same result: either 'unparsable' or 'incorrect argument'. Can't figure it out.

Thank you for your efforts! dbn

Samuel Mueller

What are the column types for grant and phase? are they dropdowns or multi selects? also, are there any errors at all on the referenced sheet, in any of those three columns?

DianeNelligan24_

The column types are dropdowns, single selection. I also double check this, they are also 'restricted' to the dropdown values. Confirming: No errors at all on the reference sheet in any of these columns.

Thank you for your help with this. dbn

Samuel Mueller

@DianeNelligan24_ Try this

=SUMIFS({SSq Fund Tracker | Award}, {SSq Fund Tracker | Award Type}, has(@cell, "Grant"), {SSq Fund Tracker | Phase}, has(@cell, "Phase II"))

DianeNelligan24_

Tried... failed. UNPARSABLE error.

This is what was typed:

=SUMIFS({SSq Fund Tracker | Award}, {SSq Fund Tracker | Award Type}, has(@cell "Grant"), {SSq Fund Tracker | Phase}, has(@cell,"Phase II"))

Samuel Mueller

@DianeNelligan24_

you missed a comma in the first Has function:

=SUMIFS({SSq Fund Tracker | Award}, {SSq Fund Tracker | Award Type}, has(@cell, "Grant"), {SSq Fund Tracker | Phase}, has(@cell,"Phase II"))

DianeNelligan24_

Good catch... I tried this (added the comma) Still no go. It seems to insist on making the 'has' all caps when I hit enter.

=SUMIFS({SSq Fund Tracker | Award}, {SSq Fund Tracker | Award Type}, HAS(@cell, "Grant"), {SSq Fund Tracker | Phase}, HAS(@cell, "Phase II"))

Thoughts?

Samuel Mueller

All caps is fine, what error are you getting this time?

The only reason this formula wouldn't work is if there is an error someone in one of the columns, or your ranges are different sizes (especially if getting incorrect argument), or maybe columns types.

Can you share a screenshot of both sheets?

Samuel Mueller

Then try separately, each function to see if any data returns

=SUMIFS({SSq Fund Tracker | Award}, {SSq Fund Tracker | Award Type}, HAS(@cell, "Grant"))

=SUMIFS({SSq Fund Tracker | Award}, {SSq Fund Tracker | Phase}, HAS(@cell, "Phase II"))

DianeNelligan24_

DianeNelligan24_

I did do each formula separately and I do get data returned. It's when I combine them that I get errors. Sigh...

Samuel Mueller

@DianeNelligan24_

try this

=SUMIFS({SSq Fund Tracker | Award}, {SSq Fund Tracker | Award Type}, @cell = "Grant", {SSq Fund Tracker | Phase}, @cell = "Phase II")

If that doesn't work, and it worked separate, then do this

=SUMIFS({SSq Fund Tracker | Award}, {SSq Fund Tracker | Award Type}, HAS(@cell, "Grant")) + SUMIFS({SSq Fund Tracker | Award}, {SSq Fund Tracker | Phase}, HAS(@cell, "Phase II"))

Samuel Mueller

and if that doesn't work then out of curiosity try

=SUMIFS({SSq Fund Tracker | Award}, {SSq Fund Tracker | Award Type}, HAS(@cell, "Grant"), {SSq Fund Tracker | Phase}, HAS(@cell, "Phase II"), {SSq Fund Tracker | Award}, ISNUMBER(@cell))

DianeNelligan24_

Okay, this is interesting: I tried all of the suggestions above, nothing worked. I was driving back to my home office and was pondering this problem. Decided to try reversing the order of the criterion, so I did Phase first, then Type. Believe it or not, that worked. Not sure why it would matter, but problem solved.

Thank you for all of your time and guidance on this. Great collaboration!

Best; dbn