AND and HAS functions in a formula

Looking for help to put the below formula into the right order. I have played around with where I put the IF(AND and HAS functions, but stays unparseable.

I need it to sum the award total if the grantor is at primary column row for the FY at row if the Status is Awarded and the Funding Source is Federal.

=SUMIFS({Award Total}, {Grantor}, [Primary Column]@row , {FY Awarded}, [Column2]@row , IF(AND({Status}, HAS(@cell , "Awarded"), ({Funding Source}, HAS(@cell , "Federal"))))

Assistance would be appreciated.

Find more posts tagged with

Accepted answers

Antonie B

@Delilah ,

There are a few issues with your formula!

The HAS function is designed for Multi Select Dropdown/Contact columns: https://help.smartsheet.com/function/has

On top of this, because you are using a SUMIFS, you do not need to create a IF(AND( section, because you already have that covered in the SUMIFS part. With the IF section, you would need to add actions for when it was true, which you did not add! For example:

"IF(AND({Status}, HAS(@cell , "Awarded"), {Funding Source}, HAS(@cell, "Federal"), "Value if True", "Value if False" "

Lastly, you have placed a parentheses wrong:

=SUMIFS({Award Total}, {Grantor}, [Primary Column]@row , {FY Awarded}, [Column2]@row , IF(AND({Status}, HAS(@cell , "Awarded"), ({Funding Source}, HAS(@cell , "Federal"))))

The one I bolded would need to be deleted if you followed the logic of the functions you used.

In your case, I would try this if the columns are not Multi-Select:

=SUMIFS({Award Total}, {Grantor}, [Primary Column]@row , {FY Awarded}, [Column2]@row , {Status}, "Awarded"), {Funding Source}, "Federal")

If it is a Multi-Select Column:

=SUMIFS({Award Total}, {Grantor}, [Primary Column]@row , {FY Awarded}, [Column2]@row , {Status}, HAS(@cell, "Awarded"), {Funding Source}, HAS(@cell, "Federal"))

There are a lot of great resources for formulas, but next to the Smartsheet University, I use this mostly:

You can search for specific functions and how to use them i.e.: https://help.smartsheet.com/function/has

This also pop-up in the sheet when you create a formula, by clicking on "Learn More"

In the Smartsheet Templates section, there is a sheet that has all the formulas added as working examples that you review, the template is called "Formula Handbook"

I hope this helps!

All comments

Antonie B

@Delilah ,

There are a few issues with your formula!

The HAS function is designed for Multi Select Dropdown/Contact columns: https://help.smartsheet.com/function/has

"IF(AND({Status}, HAS(@cell , "Awarded"), {Funding Source}, HAS(@cell, "Federal"), "Value if True", "Value if False" "

Lastly, you have placed a parentheses wrong:

=SUMIFS({Award Total}, {Grantor}, [Primary Column]@row , {FY Awarded}, [Column2]@row , IF(AND({Status}, HAS(@cell , "Awarded"), ({Funding Source}, HAS(@cell , "Federal"))))

The one I bolded would need to be deleted if you followed the logic of the functions you used.

In your case, I would try this if the columns are not Multi-Select:

=SUMIFS({Award Total}, {Grantor}, [Primary Column]@row , {FY Awarded}, [Column2]@row , {Status}, "Awarded"), {Funding Source}, "Federal")

If it is a Multi-Select Column:

=SUMIFS({Award Total}, {Grantor}, [Primary Column]@row , {FY Awarded}, [Column2]@row , {Status}, HAS(@cell, "Awarded"), {Funding Source}, HAS(@cell, "Federal"))

There are a lot of great resources for formulas, but next to the Smartsheet University, I use this mostly:

You can search for specific functions and how to use them i.e.: https://help.smartsheet.com/function/has

This also pop-up in the sheet when you create a formula, by clicking on "Learn More"

In the Smartsheet Templates section, there is a sheet that has all the formulas added as working examples that you review, the template is called "Formula Handbook"

I hope this helps!

Delilah

Thanks very much! The formula for multiselect worked.

I actually had the formula working perfectly until I had to add that second criteria of federal.

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.