Two Level "Sort" Using DSUM Function

Mr. Rogers

New Member
Joined
Sep 18, 2002
Messages
1
Be kind - I'm a newbie. Thanks for any help you can offer.

For a project that I'm managing, I have a spreadsheet with basic paid invoice information – date of check, amount paid, payee, account code. This is on Sheet 1.

I've used the DSUM function to create a report, on Sheet 2, that shows the total amount spent for each individual account. I would like to also show the amount spent each month for each individual account. There would be a column for each month from the inception of the project to the present time; Month 1 would be the starting month, Month 2 the second month, etc.

I was able to create a formula that compares the date of each check (as shown on Sheet 1) to the start date of the project and returns the current month. So, on Sheet 1, a new column gives the month number of each payment.

As noted, I know how to use DSUM on Sheet 2 to refer to a given project code and query the database on Sheet 1 for the sum of all payments under that code. I don't know how to query the database on Sheet 1 for the sum of all payments under a given code that fall within a given month. In other words, I think this is a sort within a sort.

I like the database functions because, in fact, I don't have to sort my data – I can enter it and the functions will pick up any additions or changes without my having to manually sort things again. This reduces the chance of the data changing and the report falling out of date due to a failure to manually sort the data and update and relevant formulas.

Is there a way to get this "sort within a sort" using the database functions? Just to be extra picky, I prefer not using macros.

Thanks again.
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
Hi - welcome to the board!


some generic methods:

=sumproduct((range1=condition1)*(range2=condition2)*(sumrange))

or array entered (i.e. using control + shift + enter, not just enter):

=sum(if(range1=condition1,if(range2=condition2,sumrange)))

or dsum

=(database,column_to_sum,multi_criteria_range)


Follow the link to Aladin's post for extensive info on multi conditional counting / summing:

http://mrexcel.com/board/viewtopic.php?topic=20900&forum=2

Post back if you want more...

Paddy
This message was edited by PaddyD on 2002-09-19 17:41
 

Forum statistics

Threads
1,144,363
Messages
5,723,914
Members
422,527
Latest member
JayTheKaz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top