Hello, I have what I consider a basic formula that is not working: =IF({Finish} = "", SUMIFS({MPST Savings}, {Location}, =Label@row, {Target Finish}, AND(>DATES3, DATES3, DATES3, DATES3, <=DATES4)) Yet when I put it all together it gives me #INVALID OPERATION. I am scratching my head as to the simple thing I'm missing… Thanks Silvano

If statement with sumifs not working - yet each element does

Hello, I have what I consider a basic formula that is not working:

=IF({Finish} = "", SUMIFS({MPST Savings}, {Location}, =Label@row, {Target Finish}, AND(>DATES3, <=DATES4)), SUMIFS({MPST Savings}, {Location}, =Label@row, {Finish}, AND(>DATES3, <=DATES4)))

Yet, if I enter the true portion it works:

=SUMIFS({MPST Savings}, {Location}, =Label@row, {Target Finish}, AND(>DATES3, <=DATES4))

and if I enter the false portion, it works:

=SUMIFS({MPST Savings}, {Location}, =Label@row, {Finish}, AND(>DATES3, <=DATES4))

Yet when I put it all together it gives me #INVALID OPERATION.

I am scratching my head as to the simple thing I'm missing…

Thanks

Silvano

Find more posts tagged with

Accepted answers

All comments

DKazatsky2

Hi @Silvano Meffe,

The issue is with the "IF" portion of your formula. You cannot evaluate an IF against a range, which is what you are doing in the formula. If you need to use that as the initial criteria, the formula needs to be on that sheet and looked at row-by-row.

Hope this helps,

Dave

NickStaffordPM

So maybe for two reasons

You should have an @row after the {Finish} in the intial logical statement
You could try and use ISBLANK({Finish}@row) as well as the intial logical statement

Let me know if either of these worked!

Silvano Meffe

for further clarification {Finish} references a date column and the IF statement is just checking if it is blank.

Silvano Meffe

Sadly when I added @row, either using the formula or ISBLANK, I now get #UNPARSEABLE.

Also, I would think that given all other references to other sheets are column references, without the @row so as to do the sumifs with columns, the {Finish} also should be a column reference.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.