Doing the equivalent of a SUM and a GROUP BY in Excel 1

[PTW]

Hi Everyone,

I am working on an Expense Statement. On the statement there is a line for each expense entry:

Item GL Number Amount
Entertainment 10000 $75
Parking--Airport 10001 $50
Sundries 10002 $20
Parking--Downtown 10001 $100

At the bottom right of the worksheet, I am required to give totals for these expenses by GL Number. For the example above:

GL Number Total
10000 $75
10001 $150
10002 $20

The problem I have is that the original list of expenses can be in any order, and there are dozens of possible GL Numbers to total but each person typically will only have 3-4 different ones on their expense statement. I only have about a dozen lines on the worksheet to total things up.

Any ideas on how I can do this? I'm not sure how I could do this with a SUMIF since I won't know what the GL Number is each time. In SQL I would simply do a GROUP BY.

Thanks!

 

[Zathras]

This comes close:
Assuming your data are in A1:C5 as given above, enter these into the cells indicated:

A11: GL Number
B11: Total
A12: =MIN($B$2:$B$5)
B12: =IF(A12>0,SUMIF($B$2:$B$5,A12,$C$2:$C$5),"")
A13: {=IF(A12>0,MIN(IF($B$2:$B$5>A12,$B$2:$B$5)),0)}

The formula in A13 is an array formula. Copy or type the portion inside the braces and then use Ctrl+Shift+Enter instead of plain enter. Excel will then display the formula with the curly braces as shown.

Copy the formula in A13 down as far as you like.
Copy the formula in B12 down to the same row.

If you don't like the zeros in column A, you can use Tools/Options/View to untick "Zero values" to cause them to not show.

 

[NumberCrunchingMonky]

I love SUMPRODUCT because it does what SUMIF does, but one can use a cell reference instead of having to use a fixed "text" value--a huge plus since you can have many GL numbers.

First, get a unique list of the GL numbers (select range, Data-->Filter - Advanced Filter; check "Copy to another Location" and Unique Records Only". In the bottom box type the cell where you want the unique values list pasted). Click "OK".

Let's assume you did this. Here's the list and formulas (that goes in the bottom-right corner of your sheet). The data table is below:

Y Z
1 GL# Total
20 10000 =SUMPRODUCT(($B$3:$B$6=$Y20)*($C$3:$C$6))
21 10001 PASTE
22 10002 PASTE
23 10003 PASTE


Here's your data table:

1 A B C
2 Item GL Number Amount
3 Entertainment 10000 $75
4 Parking--Airport 10001 $50
5 Sundries 10002 $20
6 Parking--Downtown 10001 $100

 

[PTW]

Thanks for the replies!

I just tried Zathras' example and it works! Holy (insert favorite sacred animal here)! I think I need to read up more on how these array formulas work.

The problem was actually a bit more complex since there are actually two sets of Expense rows in side-by-side tables, but the formula was easily modified to handle that.

NumberCrunchingMonky, thanks for the other suggestion. Unfortunately, this Excel Worksheet will actually be a user form, and so I will not be able to manually manipulate it to do any copying/manipulating of data.

 

[mscallisto]

You should realy take a look into pivot tables they may also be a nice solution and they're easy to use!

Ken Wright posted this introduction to pivot tables in thread68-738858

http://www.geocities.com/jonpeltier/Excel/Pivots/pivotstart.htm

another nice one is

http://www.cpearson.com/excel/pivots.htm

I never bothered with pivot tables until I discovered just how much time and effort they saved and you can SUM in every conceivable direction with no formula changes.

 

[rss01]

NumberCrunch

I'm giving you a star for this piece of your posting:

First, get a unique list of the GL numbers (select range, Data-->Filter - Advanced Filter; check "Copy to another Location" and Unique Records Only". In the bottom box type the cell where you want the unique values list pasted). Click "OK".

That will save me a bunch of time.

 

[PTW]

Hmm...this problem ended up a bit tougher than I thought.

Using Zathras' example code with an array formula, I was successfully able to get it working with one of my tables. However, there are actually TWO tables of data (identical size, and containing similar data), and the array formula seems to break down.

Here's the working code with one table:
{=IF(J40>0,MIN(IF($D$9:$D$33>J40,$D$9:$D$33)),0)}

Here's the code that tried to incorporate a second table, but did not work:
{=IF(J40>0,MIN(IF(($D$9:$D$33,$E$9:$E$33)>J40,($D$9:$D$33, $E$9:$E$33))),0)}

It cannot seem to evaluate the combined ranges ($D$9:$D$33,$E$9:$E$33). I tried using a Named Range, but that did not help.

Any suggestions? Since this is a user form I cannot simply take the data entered and then expect to do any sorting/filtering afterwards. Thanks!

 

[Zathras]

Without more details on what the "second table" looks like and what you expect the output to look like, there is no way anyone can help you.

 

[PTW]

Thanks for the reply.

The second table is the exact same format as the first table, except several columns over to the right. Actually, the example code I gave above is incorrect--the first table has the GL numbers in column D, and the second table has the GL Numbers in column L (not E as listed above).

Table 1 Columns: C (Description), D (GL Number), E (Amount)

Table 2 Columns: K (Description), L (GL Number), M (Amount)

In the meantime, I have simply used the kludgy but effective method of copying the data from the two tables into a single table so that the array formula works, and then hid those columns. I was hoping for a more elegant solution though.

Thank-You.

 

[Zathras]

It seems strange that array formulas don't work on multi-area arrays, but I guess that's just the way it is.

If you define four ranges like this:

glnumbers1: Sheet1!$D$2:$D$5
amounts1: Sheet1!$E$2:$E$5
glnumbers2: Sheet1!$L$2:$L$5
amounts2: Sheet1!$M$2:$M$5

Then you can modify the formulas as follows:

A11: GL Number
B11: Total
A12: =MIN(MIN(glnumbers1),MIN(glnumbers2))
B12: =IF(A12>0,SUMIF(glnumbers1,A12,amounts1)+SUMIF(glnumbers2,A12,amounts2),"")
A13: {=IF(A12>0,MIN(MIN(IF(glnumbers1>A12,glnumbers1)),MIN(IF(glnumbers2>A12,glnumbers2))),0)}

As before, the formula in A13 is an array formula. Copy or type the portion inside the braces and then use Ctrl+Shift+Enter instead of plain enter. Excel will then display the formula with the curly braces as shown.

Copy the formula in A13 down as far as you like.
Copy the formula in B12 down to the same row.