Countifs matching two separate tables

[Melimob]

Hi

I have this formula but stuck on the last condition.

I want to count based on Client Name in current table where it finds a match to:
table1 client name
& is not OOS/Lost
AND
if the country is listed both on table1 matching a table in another sheet which is called 'Prority 1'

this last bit is where I'm stuck? Grateful for advice! thank you!

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

 

[tyija1995]

Hey, I'd append your COUNTIFS formula with a SUMPRODUCT, close off the COUNTIFS after your final working constraint (Table1[OOS / Lost],"<>LOST")
and add +SUMPRODUCT(--([@Country]=Table2[Priority 1]))
Replace Table2 with the other tables name and Priority 1 I have assumed to be the column name - basically reference the table column from the other sheet. Make sure you use this formula in correlation with your main tables rows as it uses structured referencing.

Full formula:
=COUNTIFS(Table1[POA / Clients],"*"&[Client Name]&"*",Table1[OOS / Lost],"<>OOS",Table1[OOS / Lost],"<>LOST") + SUMPRODUCT(--([@Country]=Table2[Priority 1]))

 

[Melimob]

Hey, I'd append your COUNTIFS formula with a SUMPRODUCT, close off the COUNTIFS after your final working constraint (Table1[OOS / Lost],"<>LOST")
and add +SUMPRODUCT(--([@Country]=Table2[Priority 1]))
Replace Table2 with the other tables name and Priority 1 I have assumed to be the column name - basically reference the table column from the other sheet. Make sure you use this formula in correlation with your main tables rows as it uses structured referencing.

Full formula:
=COUNTIFS(Table1[POA / Clients],"*"&[Client Name]&"*",Table1[OOS / Lost],"<>OOS",Table1[OOS / Lost],"<>LOST") + SUMPRODUCT(--([@Country]=Table2[Priority 1]))

Hi Tyija

Firstly thank you so much for your reply... so it's semi working but giving a value error for first two rows then giving wrong value for the remaining. I think this is what you were talking about correlation with main tables but I don't know how to fix?

I've tried:
=COUNTIFS(Table1[POA / Clients],"*"&[@[Client Name]]&"*",Table1[OOS / Lost],"<>OOS",Table1[OOS / Lost],"<>LOST") + SUMPRODUCT(--(Table1[@Country]=Table47[Priority 1]))

so Table1 data starts on row 8
Table47 data starts on row 2
and the table which I'm entering the formula in starts on row 6
Unfortunately row 5 has 'Strategic' in under client name and then the rest of that row is blank -does this matter?

[TABLE="width: 103"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Client Name[/TD]
[/TR]
[TR]
[TD]STRATEGIC[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[/TR]
</tbody>[/TABLE]

thank you for any advice!

 

[tyija1995]

Hey,

So there are 3 tables? Then the last formula (SUMPRODUCT) will most likely be the issue now as it is using a structured reference from table 1 (so has to be written alongside the table if you will) - that's probably why the errors are for the first 2 rows (6 & 7) then as the table 1 starts on row 8 you get different messages from row 8 onwards. It's hard to visualise this with a 3rd table, have you got some link to the file?

 

[Melimob]

Hey,

So there are 3 tables? Then the last formula (SUMPRODUCT) will most likely be the issue now as it is using a structured reference from table 1 (so has to be written alongside the table if you will) - that's probably why the errors are for the first 2 rows (6 & 7) then as the table 1 starts on row 8 you get different messages from row 8 onwards. It's hard to visualise this with a 3rd table, have you got some link to the file?

waa sorry for this.

so here's how it's set up as an example:

sheet lkups:

Table47 data starts on row 2
[TABLE="width: 77"]
<tbody>[TR]
[TD]Priority 1[/TD]
[/TR]
[TR]
[TD]Bosnia[/TD]
[/TR]
[TR]
[TD]Croatia[/TD]
[/TR]
[TR]
[TD]El Salvador[/TD]
[/TR]
[TR]
[TD]Greece[/TD]
[/TR]
</tbody>[/TABLE]

sheet Project Timelines
so Table1 data starts on row 8

[TABLE="width: 256"]
<tbody>[TR]
[TD="class: xl75, width: 64"]Country[/TD]
[TD="class: xl76, width: 64"]Client Count[/TD]
[TD="class: xl77, width: 64"]POA / Clients[/TD]
[TD="class: xl78, width: 64"]OOS / Lost[/TD]
[/TR]
[TR]
[TD="class: xl79, width: 64"]Angola[/TD]
[TD="class: xl80, width: 64"]Clients (0)[/TD]
[TD="class: xl81, width: 64"]XXX Partner[/TD]
[TD="class: xl82, width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl83, width: 64"]Angola[/TD]
[TD="class: xl84, width: 64"][/TD]
[TD="class: xl85, width: 64"]ABC[/TD]
[TD="class: xl86, width: 64"]OOS[/TD]
[/TR]
[TR]
[TD="class: xl83, width: 64"]Bosnia[/TD]
[TD="class: xl84, width: 64"][/TD]
[TD="class: xl85, width: 64"]ABC[/TD]
[TD="class: xl86, width: 64"][/TD]
[/TR]
</tbody>[/TABLE]


sheet Top Clients
(this is where the formula is returning the value)
Table16 starts on row 6 (row 5 has strategic)

[TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl75, width: 64"]Client Name[/TD]
[TD="class: xl76, width: 64"]Priority 1
2019[/TD]
[/TR]
[TR]
[TD="class: xl77, width: 64"]STRATEGIC[/TD]
[TD="class: xl78, width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl79"]ABC[/TD]
[TD="class: xl80"]#VALUE![/TD]
[/TR]
</tbody>[/TABLE]


I want to match against the client in table 16 and count them from Table 1 as long as they're not 'OOS' 'Lost' and only if the country is matched on table47 priorty1 (lkups tab)

=COUNTIFS(Table1[POA / Clients],"*"&[@[Client Name]]&"*",Table1[OOS / Lost],"<>OOS",Table1[OOS / Lost],"<>LOST") + SUMPRODUCT(--(Table1[@Country]=Table47[Priority 1]))

so in above example it should return value as 1 since bosnia and ABC are on the list and are not marked as OOS/Lost

complicated or what!?

 

[tyija1995]

This has blown my brain and I can't think how to do this right now! I am sure there would be a way but I can't think what it is unfortunately...

 

[Marcelo Branco]

Try in the second column of Table16

=SUMPRODUCT(ISNUMBER(SEARCH([@[Client Name]],Table1[POA / Clients]))*(Table1[OOS / Lost]<>"OOS")*(Table1[OOS / Lost]<>"LOST")*ISNUMBER(MATCH(Table1[Country],Table47[Priority 1],0)))

Hope this helps

M.

 

[Melimob]

Try in the second column of Table16

=SUMPRODUCT(ISNUMBER(SEARCH([@[Client Name]],Table1[POA / Clients]))*(Table1[OOS / Lost]<>"OOS")*(Table1[OOS / Lost]<>"LOST")*ISNUMBER(MATCH(Table1[Country],Table47[Priority 1],0)))

Hope this helps

M.

Thank you so much Marcelo that works great!

erm, my only fear is sometimes the country on table 1 is inputted with other text. In most of my other formulas reading this column I've had to do 'contains' rather than 'exact match'.
It works ok for now since all the countries listed are using correct format but in case anyone adds in other text, is there any way to adapt the below for 'contains'?

=SUMPRODUCT(ISNUMBER(SEARCH([@[Client Name]],Table1[POA / Clients]))*(Table1[OOS / Lost]<>"OOS")*(Table1[OOS / Lost]<>"LOST")*ISNUMBER(MATCH(Table1[Country],Table47[Priority 1],0)))

thank you thank you thank you again though to you both!

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

my only fear is sometimes the country on table 1 is inputted with other text. In most of my other formulas reading this column I've had to do 'contains' rather than 'exact match'.

Try
=SUMPRODUCT(ISNUMBER(SEARCH(Table16[@[Client Name]],Table1[POA / Clients]))*(Table1[OOS / Lost]<>"OOS")*(Table1[OOS / Lost]<>"LOST")*ISNUMBER(MATCH("*"&Table1[Country]&"*",Table47[Priority 1],0)))

M.

 

[Melimob]

You are welcome. Thanks for the feedback.



Try
=SUMPRODUCT(ISNUMBER(SEARCH(Table16[@[Client Name]],Table1[POA / Clients]))*(Table1[OOS / Lost]<>"OOS")*(Table1[OOS / Lost]<>"LOST")*ISNUMBER(MATCH("*"&Table1[Country]&"*",Table47[Priority 1],0)))

M.

Hi Marcelo

Thank you again for trying to help me.

Unfortunately that didn't work

It's ok though, I'll try and advise them that they need to ensure Table1 only has the country name and nothing else.

thank you again!