Countifs formula not working for array range

[Melimob]

Hi

I have this formula which works

=COUNTIFS(Table1[[Country]:[Country]],"*"&$B184&"*",Table1[[POA / Clients]:[POA / Clients]],"<>*ABC*",Table1[[POA / Clients]:[POA / Clients]],"<>*TBC*")

but the last condition that I want to add is searching a number of columns and it returns "VALUE" error:

,Table1[[11-Feb]:[10-Feb]],"GO")

I basically want to count all the rows within the range 11-Feb to 10-Feb Columns which contain the word 'GO' matching with the country but ignoring any clients which are 'TBC' or 'ABC'?

Any ideas why this is not working?

E.g. my table is below

My formula should return a count of 2 as the Country in cell B184 is Ukraine and I'm ignoring the client 'TBC'. My range in example below is 11-FEB to 25-Feb but I have each week from 11-Feb till 10-Feb

Table 1

[TABLE="width: 320"]
<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl79, width: 64"]Country[/TD]
[TD="class: xl77, width: 64"]POA / Clients[/TD]
[TD="class: xl78, width: 64"]11-Feb[/TD]
[TD="class: xl78, width: 64"]18-Feb[/TD]
[TD="class: xl78, width: 64"]25-Feb[/TD]
[/TR]
[TR]
[TD="class: xl75, width: 64"]Ukraine[/TD]
[TD="class: xl75, width: 64"]TBC[/TD]
[TD="class: xl76"]GO[/TD]
[TD="class: xl76"][/TD]
[TD="class: xl76"][/TD]
[/TR]
[TR]
[TD="class: xl75, width: 64"]Ukraine[/TD]
[TD="class: xl75, width: 64"]ACME[/TD]
[TD="class: xl76"]GO[/TD]
[TD="class: xl76"][/TD]
[TD="class: xl76"][/TD]
[/TR]
[TR]
[TD="class: xl75, width: 64"]Ukraine[/TD]
[TD="class: xl75, width: 64"]TEST[/TD]
[TD="class: xl76"][/TD]
[TD="class: xl76"][/TD]
[TD="class: xl76"]GO[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 320"]
<colgroup><col width="64" span="5" style="width:48pt"></colgroup><tbody>[TR]
[TD="class: xl75, width: 64"][/TD]
[TD="class: xl75, width: 64"][/TD]
[TD="class: xl76"][/TD]
[TD="class: xl76"][/TD]
[TD="class: xl76"][/TD]
[/TR]
</tbody>[/TABLE]

Many thanks in advance

 

[Marcelo Branco]

I can't see a column 10-Feb in your table. I think you meant
,Table1[[11-Feb]:[18-Feb]],"GO")

As COUNTIFS requires ranges with the same size, maybe...
=SUMPRODUCT(ISNUMBER(SEARCH($B184,Table1[[Country]:[Country]]))*(Table1[[POA / Clients]:[POA / Clients]]<>"ABC")*(Table1[[POA / Clients]:[POA / Clients]]<>"TBC")*(Table1[[11-Feb]:[18-Feb]]="GO"))

Hope this helps

M.

 

[Melimob]

I can't see a column 10-Feb in your table. I think you meant
,Table1[[11-Feb]:[18-Feb]],"GO")

As COUNTIFS requires ranges with the same size, maybe...
=SUMPRODUCT(ISNUMBER(SEARCH($B184,Table1[[Country]:[Country]]))*(Table1[[POA / Clients]:[POA / Clients]]<>"ABC")*(Table1[[POA / Clients]:[POA / Clients]]<>"TBC")*(Table1[[11-Feb]:[18-Feb]]="GO"))

Hope this helps

M.

Marcelo -thank you sooooooooooooooooooooooooooo MUCH!

Works great except I want to adapt it to does not contain "ABC" instead of exact match.
I tried to wrap it with wildcards but doesn't quite work: "<>*ABC*" or <>"*ABC*"
grateful if you could let me know this last part.

and thank you again! Amazing!

 

[Marcelo Branco]

Maybe...

ISERROR(SEARCH("ABC",Table1[[POA / Clients]:[POA / Clients]]))

M.

 

[Melimob]

Maybe...

ISERROR(SEARCH("ABC",Table1[[POA / Clients]:[POA / Clients]]))

M.

Hi Marcelo

Sorry for late reply. I tried this and couldn't get it to work but it was ok as there wasn't too many variances so I just wrote them all.

However, I am now using this for another sheet where I do need to do a 'contains' as there are too many variances.

So this is where I am at:

=SUMPRODUCT(ISNUMBER(SEARCH($A6,Table1[[POA / Clients]:[POA / Clients]]))*(Table1[[POA / Clients]:[POA / Clients]]<>"ABC Partner")*(Table1[[POA / Clients]:[POA / Clients]]<>"ABC Proprietary")*(Table1[[POA / Clients]:[POA / Clients]]<>"ABC J.V.")*(Table1[[POA / Clients]:[POA / Clients]]<>"TBC")*(Table1[[POA / Clients]:[POA / Clients]]<>"BBB Partner")*(Table1[[OOS / Lost]:[OOS / Lost]]<>"OOS")*(Table1[[OOS / Lost]:[OOS / Lost]]<>"LOST")*(Table1[[Status]:[Status]]="Terminated
APAC"))

If I knew how to do does not contain or contains partial text this could be a lot cleaner. This is what I am trying to achieve but whatever I'm trying is not working:

CountIf
1) client in A6 is found in table 1 client
and
2) table 1 client does not contain 'ABC' or 'BBB'
and
3) table 1 OOS/Lost is not 'OOS' or 'Lost'
and
4) table 1 Status contains 'Terminated'

there are many variances of 'terminated' so that's why I need a 'contains' variance.

Any advice gratefully received!
thank you

 

[Melimob]

Hi Marcelo

Sorry for late reply. I tried this and couldn't get it to work but it was ok as there wasn't too many variances so I just wrote them all.

However, I am now using this for another sheet where I do need to do a 'contains' as there are too many variances.

So this is where I am at:

=SUMPRODUCT(ISNUMBER(SEARCH($A6,Table1[[POA / Clients]:[POA / Clients]]))*(Table1[[POA / Clients]:[POA / Clients]]<>"ABC Partner")*(Table1[[POA / Clients]:[POA / Clients]]<>"ABC Proprietary")*(Table1[[POA / Clients]:[POA / Clients]]<>"ABC J.V.")*(Table1[[POA / Clients]:[POA / Clients]]<>"TBC")*(Table1[[POA / Clients]:[POA / Clients]]<>"BBB Partner")*(Table1[[OOS / Lost]:[OOS / Lost]]<>"OOS")*(Table1[[OOS / Lost]:[OOS / Lost]]<>"LOST")*(Table1[[Status]:[Status]]="Terminated
APAC"))

If I knew how to do does not contain or contains partial text this could be a lot cleaner. This is what I am trying to achieve but whatever I'm trying is not working:

CountIf
1) client in A6 is found in table 1 client
and
2) table 1 client does not contain 'ABC' or 'BBB'
and
3) table 1 OOS/Lost is not 'OOS' or 'Lost'
and
4) table 1 Status contains 'Terminated'

there are many variances of 'terminated' so that's why I need a 'contains' variance.

Any advice gratefully received!
thank you

SOLVED!

Don't worry, I had another go and realised I was overcomplicating! Just did a COUNTIFS and it worked.
Here's my final formula.

thank you!

=COUNTIFS(Table1[POA / Clients],"*"&[Client Name]&"*",Table1[OOS / Lost],"<>OOS",Table1[OOS / Lost],"<>LOST",Table1[Status],"*Terminated*")

 

[Marcelo Branco]

You 're welcome. I'm glad you found out a solution.

M.