Using COUNTIFS, and/Or

Nicole Hodges

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!

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. 👍️