Need to sum the minimum value from each named range ( from 1 -50) that is larger than Zero

[sebs]

Hi

I am working on a project and need some help on a array formula!
I have named ranges from named_range_01 to named_range_50.

What i want to do is to sum the lowest value from each of these named rages if the value is above zero.

I am learning new things in excel all the time, and have been trying to learn how and when to use array formulas and this seems like the perfect solution to solve my problem.
I have searched the forums to some extent, but i am not finding a solution to my problem.

so far the only way i am getting this to work is by inserting the following formula:

=SUM(MIN(IF(range_01:range_01>0;range_01));MIN(IF(range_02:range_02>0;range_02));MIN(IF(range_03:range_03>0;range_03));MIN(IF(range_04:range_04>0;range_04))) ect and would have to continue this all the way up to 50

In my mind this is not the simplest way i could use an array formulas to solve this , is there a way i can do this without using the above formula all the way up to 50?

Hope you guys can help me!

Thanks in advance

[AliGW]

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[sebs]

Hi

Hope the new title meets the requirements

[AliGW]

Much better - thank you.

Please attach a sample workbook - instructions at the top of the page.

[CARACALLA]

Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. Just before posting, scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

[sebs]

Small sample with fictive data is attached now

I can do it as shown in the sample, but to continue this all the way up to 50 seems like i am missing out on the Array part of the array formula?

Hope you guys can help

[JeteMc]

Although this doesn't make use of the named ranges it may be easier than continuing the formula to 50 ranges.
1. Helper column G is populated using: =IF(B5=0,"",COUNTIFS(A$5:A5,1))
2. Helper column H is populated using: =B5=MIN(IF(B$5:B$79>0,IF(G$5:G$79=G5,B$5:B$79)))
Note that the helper columns may be moved and/or hidden for aesthetic purposes.
3. The sum of the min value from each data set is in cell J7 and is calculated using: =SUMIFS(B5:B79,H5:H79,TRUE)
Let us know if you have any questions.