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?

 

[awoohaw]

Please help the forum help you.

The forum needs to create your scenario manually, that takes time and can cause errors and typos, and wrong assumptions.

Please post a mini worksheet of your data and expected values using the xl2bb addin (link below)?
In a worst case post a table of data elements and expected values.

 

[Jyggalag]

here is a photo, please help:

i want the formula to return 3 for zimbabwe, 4 for USA and 2 for UK, since they have this amount of values each

 

[awoohaw]

photos/images are not a lot of help. As we have no way of knowing what your actual data is.
Are the values in the value column numbers or formulas returning numbers?

I ask this because it seems you have formatted the column as "Accounting" but we have no idea. And Zimbabwee has 4 "Accounting" Values not 3.

Images are ambiguous.

Please post data, not images.

 

[Jyggalag]

sorry, here they are with the column and rows being visible:

and no zimbabwe has 3 values, which is cell I4, I5 and I15, the rest are not numerical values

 

[awoohaw]

i asked for data not another image. Please help the forum help you!

 

[awoohaw]

try

=sum(--(B4=$H$4:$H$18)*(--($I$4:$I$18>0))

 

[Sam_D_Ben]

I guess just countifs will do the task - COUNTIFS(H4:H18,B4,I4:I18,">0")

_thanks

 

[Jyggalag]

I guess just countifs will do the task - COUNTIFS(H4:H18,B4,I4:I18,">0")

_thanks

i used this formula: COUNTIFS(H4:H18,B4,I4:I18,">0")

it works in my test sheet,

however in my real sheet it returns #VALUE for some reason, any idea why?

the values I am targeting (in what is I4:I18 above) in my real sheet are all formulas that pull in numbers, but even if I convert them to raw numbers, they still return #VALUE

 

[awoohaw]

i used this formula: COUNTIFS(H4:H18,B4,I4:I18,">0")

it works in my test sheet,

however in my real sheet it returns #VALUE for some reason, any idea why?

the values I am targeting (in what is I4:I18 above) in my real sheet are all formulas that pull in numbers, but even if I convert them to raw numbers, they still return #VALUE

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