help with super complex formula

Jyggalag

Active Member
Joined
Mar 8, 2021
Messages
445
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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?
 
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?
 
Upvote 0

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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??

1682969296967.png
 
Upvote 0
this is my real sheet:

1682969406063.png


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?
 
Upvote 0
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;"")
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,853
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top