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
=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