Countifs across multiple columns using OR
 

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

Countifs across multiple columns using OR
 

Hi Tim,

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

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.

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.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Countifs across multiple columns using OR
 

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

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

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.

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*")

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