Formula using SumsIF

Brittanyy

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")

Darren Mullen

@Brittanyy

I think this should work...

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

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