• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help needed with SUMPRODUCT/SUMIF across multiple columns with criteria

C

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 :(
 
Carltoninspired


Firstly, Welcome to the Chandoo.org forums.


Try limiting the ranges to less than the entire columns

eg:
Code: 
=SUMPRODUCT(--(data1!E1:E10000='Region Overview'!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
 
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!
 
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.
 
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.
 
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.
 
Hui said:
Carltoninspired


Firstly, Welcome to the Chandoo.org forums.


Try limiting the ranges to less than the entire columns

eg:
Code: 
=SUMPRODUCT(--(data1!E1:E10000='Region Overview'!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
Click to expand...


Hi Hui,

Can I use the same formula with wildcard.

Regards,
 
Oahmed

Can you please start a new post and attach a sample file to simplify the solution
 
You must log in or register to reply here.
Back
Top