Excel IF(OR(COUNTIF nesting limitations? 2

[TomYC]

I need to count occurrences of one of five text strings (e.g. "NICU") in one column of a row OR'ed with the existence of a number one ("1") in either of five other columns. I don't want to miss any, but I don't want to double count anything either!
I have tried with code like this, but it only goes part way and then appears to run up against a nesting limit:
=IF(OR(COUNTIF(JAN!$D2,"NICU"),COUNTIF(JAN!$G2,1)),1,IF(OR(COUNTIF(JAN!$D2,"NICU"),COUNTIF(JAN!$H2,1)),1,IF(OR(COUNTIF(JAN!$D2,"NICU"),COUNTIF(JAN!$I2,1)),1,IF(OR(COUNTIF(JAN!$D2,"NICU"),COUNTIF(JAN!$J2,1)),1,IF(OR(COUNTIF(JAN!$D2,"NICU"),COUNTIF(JAN!$K2,1)),1,IF(OR(COUNTIF(JAN!$D2,"3 MONTI"),COUNTIF(JAN!$G2,1)),1,))))))
The formula gets to be too long after this anyway!
So my question is--is there not an array solution to this?

 

[xlhelp]

Put your five text string in another column eg Q2:Q6 in Jan worksheet

then your formula would be
=SUMPRODUCT((Jan!Q2:Q4=Jan!D2)*(Jan!G2:K2=1))

If more than one cell in G2:K2 has a 1 it will give you the total count.

Canadian eh! Check out the new social forum Tek-Tips in Canada.
With the state of the world today, monkeys should get grossly insulted when humans claim to be their decendents

 

[xlhelp]

I seem to do this quite often. I only used three cells.

The formula should be
=SUMPRODUCT((Jan!Q2:Q6=Jan!D2)*(Jan!G2:K2=1))



Canadian eh! Check out the new social forum Tek-Tips in Canada.
With the state of the world today, monkeys should get grossly insulted when humans claim to be their decendents

 

[SkipVought]

Hi,

This is a row formula, correct, not a aggregation, so COUNTIF, an aggregation formula, would not be applicable, would it?

So you have 1) the D column MATCHES a value in a list or 5 values AND 2) the value 1, must exist in G:K.

So make a list, name CodeList.
[tt]
=If(IsNA(Match(JAN!$D2,CodeList,0)),0,if(sum($G2:$K2)>0,1,0))
[/tt]
See if that might work for you.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[TomYC]

Thanks for the idea of putting text strings in a separate place. But I need an OR logic, and I think SUMPRODUCT is going to give me an AND logic, no?

 

[SkipVought]

You DO want AND.

You want (the OR of the code values) AND (the OR of the 1 values)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[TomYC]

It might seem that way, and more typically a problem like this would call for AND, but it in this specific case it really, really is an OR situation. Basically I am creating a count of exclusions: if a record contains one of five text strings in one particular column, OR the record contains the number 1 in ANY of five other columns, I want to count it as ONE.
Many thanks,
TomY

 

[SkipVought]

So even if the D column contains NONE of the values, if there's a 1 in G:K, you return 1?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[TomYC]

Yup! I'm creating a count of exclusions. If an event is occurs in any of the CodeList locations, I want to exclude it (after I count it!); and if it contains the value of 1 in any of the five columns, I want to count (and ultimately exclude) it. If either of those (OR'ed) situations obtains, I'd like to count the item. Originally I thought this would be easier than a sumproduct type formula, but not I'm not so sure--

 

[SkipVought]

[tt]
=If(IsNA(Match(JAN!$D2,CodeList,0)),if(sum($G2:$K2)>0,1,0),1)
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[TomYC]

Thanks, Skip--
I think I've pieced together what I need from your earlier suggestions:
=IF(ISNA(MATCH(JAN!$D2,CodeList,0)),IF(SUM(JAN!$G2:$K2),1))
I'm still testing this, but I'm pretty confident!

 

[xlhelp]

I see you got what you were looking for. Skip, if not a star from OP; certainly one from me.

Canadian eh! Check out the new social forum Tek-Tips in Canada.
With the state of the world today, monkeys should get grossly insulted when humans claim to be their decendents

 

[TomYC]

That's for certain!

 

[SkipVought]

Glad it worked for you. Thanks!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}