sum(sumif( )) Array follow-up to severalAladin Akyurek posts

ajones

Board Regular
Joined
Oct 26, 2002
Messages
106
I saw Aladin Akyurek had used a formula of the style

=SUM(SUMIF(H$1:H$20000,{"<0",">0"}))
to get around summing errors in a long series of vlookups (index/match)


This looked really really cool and more efficient then trying to to some sort of array formula or sumproduct() and doing a check for iserror().
I assume this would be the case.

After I realized what was going on I wanted to do something like
=SUM(SUMIF(A$1:A$10,{23,34,43},B$1:B$10)) ... but bigger range

HOWEVER I really wanted to take it to the next step and put the array {23,34,43} in a single cell. Then have my sum(sumif( reference the cell. Then it would be easier for another user to change the criteria.


I tridd several variations of
=SUM(SUMIF(A$1:A$10,C12,B$1:B$10))

or trying to use some sort of index/indirect option to reference the array in c12 -> {23,34,43}. However I could not get anything to work.

Ideally I would not even have to have the brackets {} just have the user put a comma between the different criteria.

thanks for the help.

Alan
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
You can't coerce what's in a cell into an array constant, but you could use a range of cells, eg:

=SUM(SUMIF(A$1:A$10,D1:F1,B$1:B$10))

Don't forget Ctrl+Shift+Enter!
 
Upvote 0
Andrew and Domenic,

Thanks for the replys....

I am figured there had to be a way... the question is just how complex the answer would be.

I am a little confused on Kris's sample.

I tried pulling the substitue and sum options into one formula but so far nothing. I tried several variations of
=SUM(SUMIF(A$1:A$10,"{"&SUBSTITUTE(C12,"'",) &"}",B$1:B$10))

but have not gotten anywhere.

any suggestions?
 
Upvote 0
Kris's contribution uses the old Excel 4 macro function EVALUATE in a defined name. You have to use that defined name in your formula.
 
Upvote 0
Assuming that the formula is going to be entered in D12, try the following...

Select D12

Insert > Name > Define

Name: Array

Refers to:

=EVALUATE("{"""&SUBSTITUTE(Sheet1!$C12,",",""",""")&"""}")

Click Ok

Then try...

=SUMPRODUCT(SUMIF(A$1:A$10,Array,B$1:B$10))

Hope this helps!
 
Upvote 0
Alternatively, if you have morefunc.xll add-in installed...

=SUMPRODUCT(SUMIF($A$1:$A$10,EVAL("{"&D2&"}"),$B$1:$B$10))

where D2 houses a string of comma-separated numbers like: 23,34,43

If you prefer, you can invoke EVAL, written in VBA. Search for FastExcel as author on this board in combination with EVAL as search term.
 
Upvote 0
Thanks for the replies everyone. I am not sure how I want to proceed.

Dong the EVALUATE Defined name sounds interesting, but something bothers me about it. How portable is that from one computer to another if one shares the Excel file around?

I really like the EVAL solution but again I am not sure how portable that is. If someone else does not have the morefunc.xll add-in what happens?

Early on in the process I can ensure what machines have what, but later on you never know and sometimes one won't even remember that XYZ is needed.

I have even try not to use macros where different computers might access the file as the security warnings scare some of the users I work with.


I will think about it some but it sounds like EVALUATE if it is portable may be the best or just going back and putting the array in the formula.

thanks again for everyone's suggestion.

If you have any other thoughts please respond

Alan
 
Upvote 0
"Dong the EVALUATE Defined name sounds interesting, but something bothers me about it. How portable is that from one computer to another if one shares the Excel file around? "

It'll travel

"I really like the EVAL solution but again I am not sure how portable that is. If someone else does not have the morefunc.xll add-in what happens? "

Latest version of the addin has a 'travel with the workbook' option
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,938
Members
448,534
Latest member
benefuexx

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