help with super complex formula

[Jyggalag]

I have a sheet called "Number of bank accounts". Here, in row 5, I have listed "Denmark" in cell C8, "Sweden" in cell C5, "Norway" in cell D8 and so on. In the respective cells underneath this, I want to have a formula that can take the name of the cell above it and look up the country above it (Denmark in the case of cell B9) in column E:E in my other sheet, which is called "Bank Accounts". I would like for this formula to count how many times Denmark, Sweden or any of the other countries appear in column E in the sheet Bank Accounts. However, I want to it to this based on the condition that the respective value in Column L in the sheet "Bank Accounts", for the country name in column E, contains a numeric value. If it does not contain a numeric value, or if the cell value is blank, I want the formula to ignore it and not count it I use ; as a separator, so please keep this in mind.

can anybody crack this code?

 

[Jyggalag]

My guess is that you have non numbers in cells I4:I18.

I do yes, but I want my formula to ignore those and count all the numerical values, which it is capable of doing in the example i put in this thread, but it cannot in my real sheet?

 

[Jyggalag]

f

My guess is that you have non numbers in cells I4:I18.

for example, these two cells have "non numbers", but the formula still works here??

 

[Jyggalag]

this is my real sheet:

if I copy paste the values from the other sheet into this sheet, it does not return #VALUE

on the other hand, the formula also fails and just returns "0" when there are clearly around 11 values for "Sweden"

what's the error? how do i fix this? and how do i make the formula able to link to my other sheet?

 

[Jyggalag]

okay i fixed it

here is my formula: "=SUMPRODUCT(('Balance'!$C$3:$C$198='Number of accounts'!C8)*(ISNUMBER('Balance'!$S$3:$S$198))*('Balance'!$S$3:$S$198>0))"

can anybody update it, so it does the opposite? right now it counts the amount of numerical values in S3-S198

i want it to count the number of non-numerical values now

NOTE* the non-numerical values all result in the value "-" and are generated by this formula (which also generates the numerical values): =IFERROR(INDEX('FX Rates'!$A$1:$AC$1000; MATCH('Balance'!$E9; 'RATES'!$D:$D; 0); MATCH('Balance'!R$2; 'FX Rates'!$2:$2;0)) * 'Balance'!R9;"")

 

[Sam_D_Ben]

-

 

[Jyggalag]

I guess could be of the "Comma", you getting #VALUE.

my separator is a ;

 

[Sam_D_Ben]

Try ("--NOT(ISNUMBER")

 

[Jyggalag]

Try ("--NOT(ISNUMBER")

what would the full formula be?

 

[Sam_D_Ben]

what would the full formula be?

I am not sure if this work for you. Please try -