[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 715
  • Last Modified:

Using OR in a COUNTIFS statement

=COUNTIFS(Static!A2:A78438,OR(120,121),Static!B2:B78438,"NO PRICING")

This is returning 0 when there are over 1000.
Any ideas what could be wrong?

I want to count how many say NO PRICING in column B when there are either 120 or 121 in column A.

Thanks!
0
Euro5
Asked:
Euro5
1 Solution
 
Shanan212Commented:
I would say

=COUNTIFS(Static!A2:A78438,"=120",Static!B2:B78438,"NO PRICING")+COUNTIFS(Static!A2:A78438,"=121",Static!B2:B78438,"NO PRICING")

Being the easy solution
0
 
barry houdiniCommented:
You could also use this version

=SUM(COUNTIFS(Static!A2:A78438,{120,121},Static!B2:B78438,"NO PRICING"))

...especially useful if there are multiple OR items....

regards, barry
0
 
SteveCommented:
This will also work: allows for numbers between a range not just 120-121

=COUNTIFS(Static!B2:B78438,"NO PRICING",Static!A2:A78438,">=120",Static!A2:A78438,"<=121")
0
 
Euro5Author Commented:
Thanks so much!! Perfect!
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now