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

ajones

Board Regular

=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

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
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!

Have a look at Kris' contribution here...

Hope this helps!

Andrew and Domenic,

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?

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.

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!

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.

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

"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? "

Replies
4
Views
634
Replies
6
Views
253
Replies
26
Views
5K
Replies
3
Views
370
Replies
6
Views
157

1,219,892
Messages
6,150,814
Members
450,985
Latest member
Andynair7

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.

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

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