# Help needed with SUMPRODUCT/SUMIF across multiple columns with criteria

#### carltoninspired

##### New Member
Hello,

I've been trying to figure out a simple solution to summing multiple columns (F:AC) whilst matching on criteria in column E.

i've tried =SUMPRODUCT(--(data1!E:E='Region Overview'!C4),data1!F:AC) however i get an error message saying "Excel ran out of resources" despite me having plenty of resources left available.

I've also tried =SUMIF(data1!E:E,'Region Overview'!C4,data1!F:AC) which only sums the values from the first column.

I know i could repeat the formula 24 times to sum each column individually but i have to do this so many times it would take forever.

I hope this is clear enough. Any help would be greatly appreciated as i've been trying to figure this one out for days now

#### Hui

##### Excel Ninja
Staff member
Carltoninspired

Firstly, Welcome to the Chandoo.org forums.

Try limiting the ranges to less than the entire columns

eg:
Code:
``=SUMPRODUCT(--(data1!E1:E10000=&#39;Region Overview&#39;!C4),data1!F1:AC10000)``

Also I don't understand your logic of multiplying 1 column x 22'ish columns?

I don't think Excel will like that

#### carltoninspired

##### New Member
Thanks for the welcome, i've been a long time lurker and have learnt a lot from this site. This is the first time i've been stuck without being able to find an answer.

I've just tried limiting the ranges... =SUMPRODUCT(--(data1!E4:E391='Region Overview'!C4),data1!F4:AC391) i no longer get the "ran out of resources message" however the return is now just #VALUE!

#### carltoninspired

##### New Member
I've also tried =SUMPRODUCT((data1!E4:E391='Region Overview'!C4)*(data1!F4:AC391)) which also returns #VALUE! surely there must be a simple way to do a SUMIF across several columns at once in Excel? I'm really at a loss here.

#### Luke M

##### Excel Ninja
Both of your last formula "should" be working. Might be worth checking F4:AC391 and make sure there's no text strings or errors in the range.

#### carltoninspired

##### New Member
Thanks for the help, there weren't any errors or text strings however there were a few blank cells in the range, i replaced them all with a nominal value and it now works.

#### Luke M

##### Excel Ninja
Hurrah! Glad to hear you were able to get it working.

#### OAhmed

##### New Member
Carltoninspired

Firstly, Welcome to the Chandoo.org forums.

Try limiting the ranges to less than the entire columns

eg:
Code:
``=SUMPRODUCT(--(data1!E1:E10000=&#39;Region Overview&#39;!C4),data1!F1:AC10000)``

Also I don't understand your logic of multiplying 1 column x 22'ish columns?

I don't think Excel will like that

Hi Hui,

Can I use the same formula with wildcard.

Regards,

#### Hui

##### Excel Ninja
Staff member
Oahmed

Can you please start a new post and attach a sample file to simplify the solution