Countifs using a Or Function

Michael Foster

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

Paul Newcome

You are correct. The ranges need to be the same size. Apparently both @Mike Wilday and I could use a few more cups of coffee. Hahaha!

You could add two separate COUNTIFS together.

=COUNTIFS({one column}, "Paul", {live column}, 1) + COUNTIFS({other column}, "Paul", {live column}, 1)

Mike Wilday

@Paul Newcome .. Or more information. Cross-sheet references don't indicate the range size. But that is good information to know.

Paul Newcome

Can you provide more detail or some sample data?

Michael Foster

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

Mike Wilday

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

Michael Foster

Hi,

Please see below example:

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

Answer should be: 2

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

Many thanks,

Mike

Paul Newcome

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

=COUNTIFS(Lead:Implementation, "Paul")

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

Michael Foster

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

Mike Wilday

=COUNTIFS(Lead:Implementation, "Paul", Live1:Live1, 1)

Would add in the Live checkbox criterion. :)

Paul Newcome

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

Michael Foster

Wish i had positive news:

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

#InvalidRef

Mike Wilday

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

😁

Michael Foster

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.

Paul Newcome

You are correct. The ranges need to be the same size. Apparently both @Mike Wilday and I could use a few more cups of coffee. Hahaha!

You could add two separate COUNTIFS together.

=COUNTIFS({one column}, "Paul", {live column}, 1) + COUNTIFS({other column}, "Paul", {live column}, 1)

Mike Wilday

@Paul Newcome .. Or more information. Cross-sheet references don't indicate the range size. But that is good information to know.

Michael Foster

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.

Thanks both for your help.

Mike

Mike Wilday

👍️Glad our two heads could combine to help you out!