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

summing based on criteria in columns

J

joharmenezes

Member
I have a question in excel which I think may have been posted before but I am not sure what search words to use to look for it. I know it is against the forum rules to post a topic which has already been posted before but honestly I tried my best to search for it but couldn't come up with anything. So now the problem I am facing is that I do accounting work in SAP and have to sum up amounts based on General Ledger codes. It's a bit difficult to explain my problem so I have attached a sample excel file which should help to explain things better. The excel contains some GL codes and amounts in Sheet 1. In sheet 2 there are some categories with their corresponding GL codes on the right hand side. Now what I would like to do is sum up the amounts from sheet 1 for the categories in sheet 2 based on their corresponding GL codes and the summed up values should appear in the "Total" column in sheet 2 next to their categories. I know this can be done using the sumifs function but u can just imagine how tedious it would be to type in the formula for each and every category. So I would be really grateful if u could show me an easier and faster way of doing this. The sample file is at http://www.filedropper.com/book1_5


Thanks
 
Hi,


Try,


=SUMPRODUCT((Sheet1!$A$2:$A$257=Sheet2!B2:N2)*(Sheet1!$B$2:$B$257))


in sheet2 cell O2 and copy down.


Jai
 
Joharmenezes


If your wondering how that works have a read of: http://chandoo.org/wp/2011/05/26/advanced-sumproduct-queries/
 
Wow! thanks a lot, Jai9. I knew sumproduct could do it but didn't know exactly how. But thanks for resolving the issue. And Hui thanks to u too. I'll go thru that thread on sumproduct.


I am just a bit curious about one more thing. The formula u gave works perfectly fine but I tried using SUMPRODUCT(--(Sheet1!$A$2:$A$257=Sheet2!B2:N2),(Sheet1!$B$2:$B$257)) and it gave a #VALUE! error. Why so?
 
SUMPRODUCT is a little quirky about that. In the first formula, we're dealing with arrays of different sizes, but we're forcing a multiplication to occur. The array math works out, and the function continues on.


The latter formula, the two arrays are built, and SUMPRODUCT "should" multiply them on it's own. However, my best guess is that SUMPRODUCT does an internal check before trying to multiply to see if the arrays are of the same size. Since they are not, the function gives up and throws out an error.


So, the difference is whether we force the multiplication or try and let the FUNCTION do it on it's own. Goofy, I know, but that's what seems to be happening.
 
@Iron Man


I have solved your problem, look how ...

A2:A257 has GL codes (I define its name as GL)

B2:B257 has Amount (I define its name as Amt) for easy calculation purpose only.


now in total cell (Sheet2!O2) type the formula sum(sumif(GL,B2:N2,Amt))


and press Ctr+Shift+Enter (array formual)


and your problem has solved.

Here is your file with solutions...

https://www.dropbox.com/s/zfryps0jeo99qts/Copy%20of%20Book1.xlsx


Regard!


Jitendra
 
You must log in or register to reply here.
Back
Top