• 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.

SUMPRODUCT to sum multiple columns

tarynmahon

tarynmahon

Member
I have used SUMPRODUCT before to bring back the sum of the specified critera but now I need the formula to bring back the sum by adding and subtracting different columns.

The formula that I tried (that gets a #VALUE! error) is as follows;


=SUMPRODUCT(--(BWJun!M:M="Inception")+--(BWJun!M:M="Rebroked")+--(BWJun!M:M="Renewed"),--(BWJun!AF:AF="Web"),--(SUM(BWJun!S:S,BWJun!V:V)-SUM(BWJun!W:W,BWJun!X:X)))


I wont post an example as I think it will be relatively simple?
 
For the end of the formula, it looks like you want to add the values from cols S and V, but subtract the values from W and X?

=SUMPRODUCT((BWJun!M:M="Inception")+(BWJun!M:M="Rebroked")+(BWJun!M:M="Renewed"),--(BWJun!AF:AF="Web"),BWJun!S:S,BWJun!V:V,-BWJun!W:W,-BWJun!X:X)


For the first 3 arrays, since you're already doing a math operation of addition on them, there's no need for the double negative symbols (used to convert boolean True/False into numbers). Since I'm assuming the last 4 arrays are also numbers, no need for converting them either.
 
Are you using XL 2003? Prior to 2007, you can't callout entire columns within SUMPRODUCT. Also, just a side note, the formula will probably calculate faster if you limit the range to just your data or some "large enough" range, rather than the whole column.
 
No, its 2007.

Im trying to create a template that will not be used by myself going forward, therefore the less there is to have to change the better, I dont really want to be replying on somebody remembering to increase the data range for different months, I'd like them just to be able to copy in the worksheet and my summary formulas will just do all the work and update automatically.
 
Fair enough. Next question would be, are there any values currently in the data? Any text strings in columns S,V, W, or X (watch out for headers)?
 
I've tried taking out the headers but it now brings back a zero result, there should be a different figure in here
 
It's only bad because SUMPRODUCT is trying to do a math operation of something like:

(0+0+0)*0*TextString

and that's causing a problem.


Also, I see an error in my previous formula. I wrote it as multiplying the 4 columns, but I think you just wanted to add/subtract them.

=SUMPRODUCT((BWJun!M:M="Inception")+(BWJun!M:M="Rebroked")+(BWJun!M:M="Renewed"),--(BWJun!AF:AF="Web"),BWJun!S:S+BWJun!V:V-BWJun!W:W-BWJun!X:X)


You could also try to debug, by changing the ranges temporarily to something small that includes a row you know should be counted, and then use the formula - evaluate formula tool to step through the calculation and see what's not working.
 
You must log in or register to reply here.
Back
Top