SUMPRODUCT to count dependent validation list column with wrong items

[drom]

Hi and Thanks in advance!

• I say SUMPRODUCT but maeby there is one other better way
  • I know how to do this using a UDF (but I do not want to use VBA for this query

My query:

I have a table with some rows and columns
In on of my Columns I have a validation list
And this column contains a conditional formatting to colour if a cell does not belong anymore to the items inside the list (validation list)


My Validation list shows as follows;

• =rc_Teams

My conditional formatting for this column shows as follows:

• =OR(F13="";COUNTIF(rc_Teams;F13)=0)
• rc_Teams was a named columns inside a table where I use to have many foorball teams

in F4, I used to have the following formula:

• =SUMPRODUCT(--(COUNTIF(rc_Teams ; TbRowData[Teams] )=0);--( TbRowData[Teams] <>""))+SUMPRODUCT(--( TbRowData[Teams] =""))

This way I used to know how many cells were colored in Columns F of the table named TbRowData


Everything worked perfect


Now the column Teams is gonna have a validation list based in other column (Column E, Country)

• So if I am tryng to get a validation list for the Spanish Teams
  • If E13 Shows Spain F13 is gonna show only the spanish teams
• The same way the fomatting will be appliyed if eg Man United is on F15 and E15 <> than England

I do this using as validation list:

• =IF(E13="";INDIRECT("Sorry");OFFSET(xAA;MATCH(E13;rc_Country;0);1;COUNTIF(rc_Country ;E13);1))
• xAA is the named cells for the Upper left cell of the tables containg Countries and Teams
  • Countries on the first columns
  • Teams on the 2nd
  • This table is always sorted first by Country them by Teams

As conditional formatting I amd using:

• =OR(F13="";COUNTIF((OFFSET(xAA;MATCH(E13;rc_Country;0);1;COUNTIF(rc_Country;E13);1));F13)=0)

Both the new validation list and the new conditional formatting are working very well (no problem)


Now I would like to use in F4 a formula to let me know how many cells are not ok

But this is where I have problem

I know I can filter the table in column F by the colour I am using when the validation list shows a non valid item
Because the conditional formatting works very well

But I would like to have a formula the same way I use to have when I was usinf my table for only one country, So when I did not care for the country

so instead of:

• =SUMPRODUCT(--(COUNTIF(rc_Teams ; TbRowData[Teams] )=0);--( TbRowData[Teams] <>""))+SUMPRODUCT(--( TbRowData[Teams] =""))
  • The prior formula

I need a new formula

Obviously more complex, Any IDEA which one ?








=O(G13="";CONTAR.SI((DESREF(xAA;COINCIDIR(T13;rcVAL_CuentaContableFinca;0);1;CONTAR.SI(rcVAL_CuentaContableFinca;T13);1));G13)=0)

 

[drom]

I am gonna use a 3rd column co concatenate Country and Team and this way is gonna be easy