SUMIFS with Arrays

The formulas below are running correctly, but I know there is a way to clean these up to more efficient calculations.

you will see below that I am looking for a sum of an amount in column M, with 5 OR-variables in column C and 45 OR-variables in column A. There are also ranges of variables in column C that may help in cleaning this formula up, in which I am looking for values from 320-339,350-360,401-403,610-618,761 and 921.

ANY help you can provide would be much appreciated. thanks!


+SUM(SUMIFS(M:M,C:C,"*76030",A:A,{"320*","321*","322*","323*","324*","325*","326*","327*","328*","329*","330*","331*","332*","333*","334*","335*","336*","337*","338*","339*","350*","351*","352*","353*","354*","355*","356*","357*","358*","359*","360*","401*","402*","403*","610*","611*","612*","613*","614*","615*","616*","617*","618*","761*","921*"}),
SUMIFS(M:M,C:C,"*76031",A:A,{"320*","321*","322*","323*","324*","325*","326*","327*","328*","329*","330*","331*","332*","333*","334*","335*","336*","337*","338*","339*","350*","351*","352*","353*","354*","355*","356*","357*","358*","359*","360*","401*","402*","403*","610*","611*","612*","613*","614*","615*","616*","617*","618*","761*","921*"}),
SUMIFS(M:M,C:C,"*76032",A:A,{"320*","321*","322*","323*","324*","325*","326*","327*","328*","329*","330*","331*","332*","333*","334*","335*","336*","337*","338*","339*","350*","351*","352*","353*","354*","355*","356*","357*","358*","359*","360*","401*","402*","403*","610*","611*","612*","613*","614*","615*","616*","617*","618*","761*","921*"}),
SUMIFS(M:M,C:C,"*76033",A:A,{"320*","321*","322*","323*","324*","325*","326*","327*","328*","329*","330*","331*","332*","333*","334*","335*","336*","337*","338*","339*","350*","351*","352*","353*","354*","355*","356*","357*","358*","359*","360*","401*","402*","403*","610*","611*","612*","613*","614*","615*","616*","617*","618*","761*","921*"}),
SUMIFS(M:M,C:C,"*76034",A:A,{"320*","321*","322*","323*","324*","325*","326*","327*","328*","329*","330*","331*","332*","333*","334*","335*","336*","337*","338*","339*","350*","351*","352*","353*","354*","355*","356*","357*","358*","359*","360*","401*","402*","403*","610*","611*","612*","613*","614*","615*","616*","617*","618*","761*","921*"}))

Try

=SUM(SUMIFS(M:M,C:C,{ "*76030";"*76031";"*76032";"*76033";"*76034" },A:A,{"320*","321*","322*","323*","324*","325*","326*","327*","328*","329*","330*","331*","332*","333*","334*","335*","336*","337*","338*","339*","350*","351*","352*","353*","354*","355*","356*","357*","358*","359*","360*","401*","402*","403*","610*","611*","612*","613*","614*","615*","616*","617*","618*","761*","921*"}))

Originally Posted by Jonmo1

Try

=SUM(SUMIFS(M:M,C:C,{ "*76030";"*76031";"*76032";"*76033";"*76034" },A:A,{"320*","321*","322*","323*","324*","325*","326*","327*","328*","329*","330*","331*","332*","333*","334*","335*","336*","337*","338*","339*","350*","351*","352*","353*","354*","355*","356*","357*","358*","359*","360*","401*","402*","403*","610*","611*","612*","613*","614*","615*","616*","617*","618*","761*","921*"}))

is there a way to combine the second array into groupings (ie. 320-339,350-360,401-403,610-618,761 and 921)?

Not that I'm aware of.

Are you looking for 'efficiency' or 'simplicity' ?
The do not necessarily go hand in hand.

Can you post a sample workbook?

Originally Posted by Jonmo1

Not that I'm aware of.

Are you looking for 'efficiency' or 'simplicity' ?
The do not necessarily go hand in hand.

Can you post a sample workbook?

it is a very large workbook, so I would be looking for efficiency due to calculation time.

I can't imagine it getting too much more efficient than it already is.
It's just alot of comparisons to make, no matter how you slice it.

Perhaps a helper column with some sort of lookup?


Can you attach a small sample book, with only 20 or so rows of relevant data?

I'm thinking something like this in a helper column.

Say column B is
=IFERROR(LOOKUP(A1,{0,320,340,350,361,401,404,610,619,761,762,921,922},{0,1,0,1,0,1,0,1,0,1,0,1,0}),0)

Then use that helper column as a criteria of 1.
=SUM(SUMIFS(M:M,C:C,{ "*76030";"*76031";"*76032";"*76033";"*76034" },B:B,1)