# Countifs using a Or Function

✭✭✭✭

Can someone please tell me what the formula for the following criteria would be please:

=Countifs(OR(Range1:Range1,@cell,Range2:range2,@cell),Range3:range3,@cell)

Countifs

If Range 1 or 2 has a value of @cell, then count the occurances

Any help would be very helpful.

Many thanks

Mike

«1

• ✭✭✭✭✭✭

Can you provide more detail or some sample data?

• ✭✭✭✭

Hi Paul,

I have two columns of data so i am trying to count the occurrences of a name in the two columns. Example

Col1 Col2

Mike Robert

Paul Paul

Mike Paul

Looking for how many times Paul is recorded in both columns - answer would be 3.

Many thanks,

Mike

• ✭✭✭✭✭✭

Could you share a screenshot of your data? So we can make a better guess at what you're trying to do?

• ✭✭✭✭

Hi,

I Want to count the number of times Paul is used on projects as wither lead or Implementation for Live Projects.

I think i need to use a OR function for Lead or Implementation.

Many thanks,

Mike

• ✭✭✭✭✭✭

Based on your above screenshot... Shouldn't the number of times Paul is found be 3? Or are you just counting the Implementation column?

To count two columns like this, you can use something such as...

But this would provide a result of 3 based on your screenshot and not 2.

• ✭✭✭✭

No because part of the condition and count is if the project is Live (ticked).

• ✭✭✭✭✭✭

Would add in the Live checkbox criterion. :)

• ✭✭✭✭✭✭

Duh. My mistake. I completely missed that part. Sorry about that. @Mike Wilday's last one should be it.

• ✭✭✭✭

=COUNTIFS({POSG Pack Range 4}, [Primary Column]3, {POSG Pack Range 2,1})

#InvalidRef

• ✭✭✭✭✭✭

You need to remove the ,1 from the range...

=COUNTIFS({POSG Pack Range 4}, [Primary Column]3, {POSG Pack Range 2}, 1)

Just make sure you didn't actually name that second cross sheet reference with the ,1

😁

• ✭✭✭✭

No comes up with Incorrect Argument Set - it doesn't like that the range has two columns in range 4. If I change the range to just 1 column it works straight away. That's why I thought it would require an OR nested statement.

• ✭✭✭✭

Hi Paul /Mike

+ two countifs together has solved the mystery - surprised I couldn't use an OR function but I have got to my destination.