DSUM database field across multiple ranges

Hey everyone,

I've been stuck on this problem for a while today. I'm trying to use DSUM to give me totals in a database. The problem seems to be that the DSUM function needs to have the database columns in one continuous block; that is, you cant compose a database from different ranges. Is this true?

To explain, I've attached an example.

What I want to do is check the "Type" column for type a and add all of the values found to give a total. Is this possible or do the columns need to be next to each other? I keep getting #VALUE.

Thanks!

Thanks for the quick reply

Sorry I should've stated that I knew about the criteria layout part (Column heading and then criteria). I can get it working fine if I select the database as one block, but if for example I want just the two columns to be the database, I hold select the first range, hold control and select the second. This results in a #VALUE in the database field. The reason I'm trying to do it this way is that the columns in between contain weekly data. I want to look at one week at a time for each dsum that I do, i.e. the column would move to the right for each week (so the 'other data' column would be checked next).

Does that make sense?

Well I mean I would like the database to exclude the in-between columns as they would give false results.

So I want the 'database' selection to span two seperate ranges as i need seperate calculations for each week.

A new example is attached

With a bit of tweaking that last solution worked perfectly. All I had to do was copy down and then strip out the $ signs and copy across. It's a perfect solution except now it takes forever to calculate as each formula checks 22,000 lines

Oh well! Thanks a lot for solving my problem!