Countifs across multiple columns using OR

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
 
C

Claus Busch

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.

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

Alfred Heiligenbrunner

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top