I'm putting together a sheet that calculates whether a group of enterprises meets certain turnover thresholds in several countries.
Column A are the countries (line 2=global; line 4-34 = EU member states); columns C-H are each enterprise's turnover (globally and in each EU member state).
Four criteria must be met, I'm struggling with no (iii). By way of background, criterion (ii) is:
(ii) a combined turnover of all the merging firms over € 100 million in each of at least three Member States
I found the solution for this here.
{=IF(SUM(IF(MMULT(C4:H31+0;TRANSPOSE(COLUMN(C4:H31)^0))>=100*10^6; 1; 0))>=3; TRUE; FALSE)}
Criterion (iii) is
(iii) a turnover of over €25 million for each of at least two of the firms in each of the three Member States included under (ii)
When splitting up the two criteria in their own arrays I get correct true/false values for each country: Combined turnover over 100 Mio in a member state
{=IF(MMULT(C4:H31+0;TRANSPOSE(COLUMN(C4:H31)^0))>=100*10^6;TRUE;FALSE)}
and
turnover for each of at least two of the firms of more than 25 Mio in a member state
{=IF((MMULT(IF(C4:H31>=25*10^6;1;0);TRANSPOSE(COLUMN(C7:H34)^0)))>=2;TRUE;FALSE)}
However combining these two formulas into one with IF(AND()) doesn't work
{=IF(AND(MMULT(C4:H31+0;TRANSPOSE(COLUMN(C4:H31)^0))>=100*10^6;(MMULT(IF(C4:H31>=25*10^6;1;0);TRANSPOSE(COLUMN(C4:H31)^0)))>=2);TRUE;FALSE)}
Is there a way of doing this, either by using arrays (preferred) or VBA?
Thanks a lot!
ElEsido
