Formula using SumsIF

Hi Everyone, I need assistance on a formula

I want to sum the numbers in the "Qty of Monitors" column if the Office column is "SOMA" and when the dropdowns are " Acer AND Viewsonic" are both selected in the Monitor column.

Formula I have put together=SUMIFS({Qty of Monitor}, {Office}, "SOMA", {Monitor}, "Viewsonic", {Monitor}, "Acer")

Find more posts tagged with

Accepted answers

Darren Mullen

@Brittanyy

I think this should work...

=SUMIFS({Qty of Monitor}, {Office}, "SOMA", {Monitor}, and(has(@cell,"Viewsonic"),has(@cell, "Acer")))

All comments

Darren Mullen

@Brittanyy Is the Monitor column a single selection or a multi selection dropdown?

Brittanyy

Hi @Darren Mullen its a multi selection dropdown.

Darren Mullen

@Brittanyy

I think this should work...

=SUMIFS({Qty of Monitor}, {Office}, "SOMA", {Monitor}, and(has(@cell,"Viewsonic"),has(@cell, "Acer")))

Brittanyy

@Darren Mullen Thanks for assistance

When I plugged in the formula it showed the sum of "0" when it should be "4".

Darren Mullen

@Brittanyy Check that your cross sheet references are assigned correctly.

I checked this formula syntax in a sheet and it worked fine, so my guess is that either the cross sheet ranges aren't assigned properly or the other question I have is what type of column is "Office" ?

Brittanyy

@Darren Mullen The Office column is a drop-down column.

But, for some odd reason the formula is populating correctly now. Thank you so much for your assistance!!!

Darren Mullen

@Brittanyy Great! Sometimes formulas with cross sheet references can lag a little bit or may not show the proper calculation until you refresh the sheet after saving. Probably what happened

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.