Countifs across multiple columns using OR

  • Thread starter Thread starter timotero
  • Start date Start date
T

timotero

Don't know why I am having troulbe figuring this out. In the data below:

Emp FT/PT City Plan State Plan
Joe Full Time Plan1 PlanB
Carrol Full Time No Plan PlanA
Ted Full Time Plan2 No Plan
Alice Part Time Plan2 PlanC
Victor Full Time No Plan No Plan
Carla Part Time Plan 1 Plan C
AJ Part Time No Plan No Plan
Bob Full Time Plan 3 No Plan

I'd like to count all instances where there is either a city plan or a state plan (or both), and the employee is Full Time. So, in this example, the answer would be 4, as only Joe, Carrol, Ted, and Bob meet this criteria.

Thank you,

tim
 
Hi Tim,

Am Mon, 2 Feb 2015 14:37:00 -0800 (PST) schrieb (e-mail address removed):
Don't know why I am having troulbe figuring this out. In the data below:

Emp FT/PT City Plan State Plan
Joe Full Time Plan1 PlanB
Carrol Full Time No Plan PlanA
Ted Full Time Plan2 No Plan
Alice Part Time Plan2 PlanC
Victor Full Time No Plan No Plan
Carla Part Time Plan 1 Plan C
AJ Part Time No Plan No Plan
Bob Full Time Plan 3 No Plan

I'd like to count all instances where there is either a city plan or a state plan (or both), and the employee is Full Time. So, in this example, the answer would be 4, as only Joe, Carrol, Ted, and Bob meet this criteria.
Click to expand...

try:
=COUNTIFS(B1:B10,"Full Time",C1:C10,"Plan*")+COUNTIFS(B1:B10,"Full Time",D1:D10,"Plan*")-COUNTIFS(B1:B10,"Full Time",C1:C10,"Plan*",D1:D10,"Plan*")


Regards
Claus B.
 
Claus Busch schrieb am 03.02.2015 08:32:04 mit Betreff "Re: Countifs
across multiple columns using OR":
Hi Tim,

Am Mon, 2 Feb 2015 14:37:00 -0800 (PST) schrieb (e-mail address removed):


try:
=COUNTIFS(B1:B10,"Full Time",C1:C10,"Plan*")+COUNTIFS(B1:B10,"Full Time",D1:D10,"Plan*")-COUNTIFS(B1:B10,"Full Time",C1:C10,"Plan*",D1:D10,"Plan*")
Click to expand...
Also this might work:
=COUNTIFS(B1:B10,"Full Time")-
COUNTIFS(B1:B10,"Full Time",C1:C10,"No Plan",D1:D10,"*No Plan")

The "*" in "*No Plan" for D1:D10 is necessary if the data there really
contain leading blanks, as in the given example data.

Regards,
Alfred
 
Back
Top