COUNTIFS with Multiple acceptable values for each range

retrac_ted

I am trying to determine the correct syntax for a query that is looking for acceptable lists of values across two columns.

I need a count of all rows that have one of three values in Column A and one of 33 values in Column B.

How would I format that?

Nic Larsen

Same formula - just use the cross sheet reference to bring in those columns so it'll look like {Column A Range} or however you name it. I'd start with smaller OR statements just to confirm its working before you add all conditions.

Nic Larsen

It would be something like this:

=COUNTIFS(ColumnA:ColumnA, OR(@cell = "Value 1", @cell = "Value 2", etc..), ColumnB:ColumnB, OR(@cell = "Value 1", @cell = "Value 2", and so on)

retrac_ted

Thanks, Nic - I will give that a shot!

retrac_ted

Here's an added wrinkle - what if I am referring to another sheet for these calculations?

Nic Larsen

Same formula - just use the cross sheet reference to bring in those columns so it'll look like {Column A Range} or however you name it. I'd start with smaller OR statements just to confirm its working before you add all conditions.

retrac_ted

That worked. Thank you so much!

retrac_ted

Boom! Thank you so much!

Here's the final formula:

=COUNTIFS({Sub-Category}, OR(@cell = "Laptop", @cell = "Desktop", @cell = "Media Player"), {Purchase Request ID}, OR(@cell = "FY23-217", @cell = "FY23-397", @cell = "FY23-412", @cell = "FY23-414", @cell = "FY23-445", @cell = "FY23-463", @cell = "FY23-500", @cell = "FY23-511", @cell = "FY23-512", @cell = "FY23-513", @cell = "FY23-653", @cell = "FY24-063", @cell = "FY24-235", @cell = "FY24-414", @cell = "FY24-415", @cell = "FY24-413", @cell = "FY24-417", @cell = "FY24-416", @cell = "FY24-173", @cell = "FY24-021", @cell = "FY24-118", @cell = "FY24-170", @cell = "FY24-277", @cell = "FY24-371", @cell = "FY24-425", @cell = "FY23-476", @cell = "FY23-590", @cell = "FY23-385", @cell = "FY23-062-Win11", @cell = "FY24-594", @cell = "FY24-595", @cell = "FY24-122", @cell = "FY24-237")

Nic Larsen

Glad to hear it worked