Combining Countif & OR Function

nwar

I am trying to write a formula that will count the number of rows where either column A (External Successor) is not blank OR column B (Internal successor) is not blank.

=COUNTIF(OR({Internal Bench} = <>"", {External Bench} = <>""))

Michael Culley

Are you looking for something like this?

=COUNTIF({Internal Bench},<>"") + COUNTIF({External Bench},<>"")

nwar

HI Michael,

I dont want to add the two countifs together, because that will count some rows twice if they have both external and internal bench not blank.

I want to count if external OR internal bench are not blank. But if External and internal bench are both not blank then i want it to only count that row once. Any ideas?

Michael Culley

This may be a goofy way to do it but what about this?

=COUNTIFS([Internal Bench]:[Internal Bench], <>"", [External Bench]:[External Bench], "") + COUNTIFS([Internal Bench]:[Internal Bench], "", [External Bench]:[External Bench], <>"") + COUNTIFS([Internal Bench]:[Internal Bench], <>"", [External Bench]:[External Bench], <>"")

Michael Culley

I messed it up:

Here you go

=COUNTIFS([Internal Bench]:[Internal Bench], <>"", [External Bench]:[External Bench], "") + COUNTIFS([Internal Bench]:[Internal Bench], "", [External Bench]:[External Bench], <>"") + COUNTIFS([Internal Bench]:[Internal Bench], <>"", [External Bench]:[External Bench], <>"")

nwar

I think this is counting rows where:

1. Internal bench is not blank and external bench is blank PLUS
2. Internal bench is blank and external bench is not blank PLUS
3. Internal bench and external bench are both not blank.

So any row where Internal Bench and External bench are both not blank is being counted more than once.

Do you think there is a way to use an OR function and look counting if internal OR external are not blank?

I so appreciate your help!!!