Hello! I am trying to total anything from a sheet that is BLANK in "Supplier Email" AND Priority is HIGH AND, IF any of the following are checked: * Completed (AP) * Invite Sent * Will Not Use Using the formula below, it doesn't seem to work. =COUNTIFS({Supplier Email}, <>"", {Priority}, [Total Suppliers]@row, (OR{Completed (AP)}, 0, OR{Invite Sent}, 0, OR({Will Not Use}, 0,) Any help is appreciated! Thank you!

Using COUNTIFS, and/Or

Hello!

I am trying to total anything from a sheet that is BLANK in "Supplier Email" AND Priority is HIGH

AND, IF any of the following are checked:

Completed (AP)
Invite Sent
Will Not Use

Using the formula below, it doesn't seem to work.

=COUNTIFS({Supplier Email}, <>"", {Priority}, [Total Suppliers]@row, (OR{Completed (AP)}, 0, OR{Invite Sent}, 0, OR({Will Not Use}, 0,)

Any help is appreciated! Thank you!

Find more posts tagged with

Accepted answers

All comments

Bassam Khalil

Hi @Nicole Hodges

Hope you are fine, Could you please add a screenshot for your sheet.

Paul Newcome

Are the three checkboxes separate columns?

I also notice that your description indicates "Supplier Email" is blank, but your formula indicates "Supplier Email" is not blank.

Is it safe to assume that the data in [Total Suppliers]@row is "HIGH"?

Nicole Hodges

AH yes @Paul Newcome - you are right on both of those:

I want the "Supplier Email" NOT Blank and yes "total suppliers" is "HIGH" Here is a screen shot attached

Thank you!!

Paul Newcome

I would suggest inserting a helper column that can later be hidden. A checkbox column would be easy enough. In this helper column you would use an IF/OR formula to check the box if it meets any of the three checkbox criteria. Then you would reference this helper column in your cross sheet formula.

=IF(OR([Completed (AP)]@row <> 1, [Invite Sent]@row <> 1, [Will Not Use]@row <> 1), 1)

=COUNTIFS({Supplier Email}, <>"", {Priority}, [Total Suppliers]@row, {Helper Column}, @cell = 1)

Nicole Hodges

Hm it seems to be counting them all even if one of the columns is checked @Paul Newcome any ideas?...

Paul Newcome

My apologies. I misread your initial post.

=IF(OR([Completed (AP)]@row = 1, [Invite Sent]@row = 1, [Will Not Use]@row = 1), 1)

Nicole Hodges

Thank you so much @Paul Newcome !

Paul Newcome

Happy to help. 👍️

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.