Taking 1 Criteria and counting how often another occurs when Criteria 1 is met

Cayla Davis

I am trying to take if Canada (on a separate Smartsheet) is found, and if Africa is found (on a separate Smartsheet), count up how often it occurs

This is the formula that I am using but i think its counting Canada on top of Africa

=COUNTIFS({Graphical Behavior Analysis: 1S Graphical Range 1}, HAS(@cell, "Canada")) + COUNTIFS({Graphical Behavior Analysis: 1S Graphical Range 2}, CONTAINS([Countries Booked]@row, @cell))

I've tried changing the + to a , but it comes back as unparsed. I was looking in the Smartsheet Community for an answer but i cant find one that matches with what I am trying to do.

Troy Scott

Your formula would certainly add the returned values (it would work like an OR condition, not AND).
It appears you are only giving one condition to two COUNTIFS formula (like a COUNTIF) where you need to supply COUNTIFS with all the AND conditions.
Try the following, it should at least get you closer:
=COUNTIFS({Graphical Behavior Analysis: 1S Graphical Range 1}, HAS(@cell, "Canada"),{Graphical Behavior Analysis: 1S Graphical Range 2}, CONTAINS([Countries Booked]@row, @cell))

Cayla Davis

Thank you Troy, something seems off… if i want to gather another column to check for the data, i am trying this

=COUNTIFS({Graphical Behavior Analysis: 1S Graphical Range 1}, HAS(@cell, "Canada"), {Graphical Behavior Analysis: 1S Graphical Range 3}, CONTAINS([Countries Booked]@row, @cell), OR({Graphical Behavior Analysis: 1S Graphical Range 4}, CONTAINS([Countries Booked]@row, @cell)))'

Am i using the OR function properly?

KPH

There is a problem with the OR. Troy's formula will work for the original requirement and count the rows where both things are true

1S Graphical Range 1 has Canada

and

1S Graphical Range 3 contains the value in Countries Booked

You can use an OR within your COUNTIFS, however the range for both things in the OR must be the same (for example an acceptable use would be to count if Range 3 is Africa OR Brazil). In your case the range is different but the thing you are looking for is the same (Range 3 or Range 4 is Africa).

Instead, you can create two COUNTIFS (which is what you were doing originally for the other requirement) and use one for Range 3 and the other for Range 4.

=COUNTIFS({Graphical Behavior Analysis: 1S Graphical Range 1}, HAS(@cell, "Canada"),{Graphical Behavior Analysis: 1S Graphical Range 3}, CONTAINS([Countries Booked]@row, @cell))

+

COUNTIFS({Graphical Behavior Analysis: 1S Graphical Range 1}, HAS(@cell, "Canada"),{Graphical Behavior Analysis: 1S Graphical Range 4}, CONTAINS([Countries Booked]@row, @cell))

The first part is as above. The part after the + (in bold) adds on the count of

1S Graphical Range 1 has Canada

and

1S Graphical Range 4 contains the value in Countries Booked

Could there be rows where both Range 3 and Range 4 contain the same value? If so, they will be counted twice. To avoid that, you can subtract the count of rows where both things are true:

=COUNTIFS({Graphical Behavior Analysis: 1S Graphical Range 1}, HAS(@cell, "Canada"),{Graphical Behavior Analysis: 1S Graphical Range 3}, CONTAINS([Countries Booked]@row, @cell))

+

COUNTIFS({Graphical Behavior Analysis: 1S Graphical Range 1}, HAS(@cell, "Canada"),{Graphical Behavior Analysis: 1S Graphical Range 4}, CONTAINS([Countries Booked]@row, @cell))

-

COUNTIFS({Graphical Behavior Analysis: 1S Graphical Range 1}, HAS(@cell, "Canada"),{Graphical Behavior Analysis: 1S Graphical Range 4}, CONTAINS([Countries Booked]@row, @cell),{Graphical Behavior Analysis: 1S Graphical Range 3}, CONTAINS([Countries Booked]@row, @cell))

I hope that gets you a step closer.