How DSUM criterias work

[Levdan]

I'm having a really annoying issue with DSUM and I'm hoping I'm just misunderstanding something. A quick background:

I've built a financial distribution model in Open Office Calc. Since most of my colleagues work in Excel, I've exported the format to Microsoft's version. Everything is working fine apart from how DSUM behaves. Part of the raw data for the calculation is a sheet with the population numbers for a large number of cities. As part of working with the model, users need to enter what cities they want to include in a particular calculation. The way I built it in Open Office is nothing flashy or elegant, but it gets the job done: I have a DSUM, that picks data from the big list of cities. Since I don't know exactly how many cities people will want to include, I've included a number of extra rows in the criteria table. When a user enter a city in the designated table, this gets transplanted to the criteria table and DSUM returns what I would expect: The total population of the cities the user wanted.

But when I change to Excel, Microsoft ruins everything.

Turns out, when DSUM in Excel encounters a blank cell in the criteria table it just shrugs its metaphorical shoulders and sums everything in the data table. I know this, because if I manually change the criteria table to only include rows that have cities entered into them, it returns the correct total. But as soon as I extend the critera table to an empty row, I might as well not include any criteria at all because DSUM just sums up the populations of all the cities in the data table.

So my question is: Is there any way around this? So if my critera table runs from row 1 to 5 and I have cities entered into four of the rows, is there any way to make Excel understand that I in fact only want the populations of those four cities summed up?

Any advice would be greatly appreciated!

[Richard Buttrey]

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

[Levdan]

Aight, here's an example. In E8 and I8 I have DSUMS. The first one uses Criteria table 1, the second uses Criteria table 2. Notice how the Criteria table are identical to one another, and yet the sums are different. The reason for the difference is that in E8 I have only included rows 3 and 4, which makes Excel sum the population of the cities I want. In I8 I have also included row 5 and 6 in the criteria table. For some reason that completely escapes me, Excel takes the empty rows in the critera table as a reason to simply sum all the populations from the Data table. This is highly annoying, since I don't know the exact nmber of cities a user might want to include in a calculation, so I have to leave some space for that in the critera table. This works just fine in Open Office: The exact same code there would give the same sum in both E8 and I8.

Am I missing something?

[Glenn Kennedy]

The blank rows are interpreted by Excel as "any value". So you have to convince Excel to stop at the last non-empty row.

One way. Clear EVERYTHING else out of the columns containing your criteria table. Set up a named ange (I called it Range). CTRL-F3 to view/edit:

=Blad1!$I$2:INDEX(Blad1!$K:$K,MAX(COUNTA(Blad1!$I:$I),COUNTA(Blad1!$J:$J),COUNTA(Blad1!$K:$K))+ROW(Blad1!$I$2)-ROW(Blad1!$I$1))

Then use the DSUM formula:

=DSUM(A2:C6,3,Range)

[Glenn Kennedy]

This is better, using a pre-set range and a bit of shading to show the range of the criteria table.

[Levdan]

Thanks a lot for the assistance!

Although, I have to admit I'm not quite up to speed with these versions of Excel: Where exactly do I go to define a range the way you do in your examples, Glen?

[Bosco]

Or…………..

In E8, enter formula and then copy to I8

=DSUM($A2:$C6,3,E2:INDEX(G:G,MATCH("*",F:F,-1)))

Regards
Bosco

[Glenn Kennedy]

Thst only works if the last non blank row is always in column F, doesn't it?

[Richard Buttrey]

Surely this is consistent with criteria range rules where criteria on different rows are OR criteria and on the same row as an AND criteria.

With I2:K6 you are including blank values which Excel regards as an Or anything.

Use a dynamic range name for the 3rd element of the DSUM criteria. i.e.

=OFFSET(Blad1!$J$2,0,0,COUNTA(Blad1!$J:$J),2)

[Glenn Kennedy]

... and on the rest of column F being blank.

[Glenn Kennedy]

... and that, too, only works if column F has the last non-blank entry.

[Richard Buttrey]

Sorry Glenn I don't follow,

The dynamic range name using the Offset function necessarily means that the criteria range is only as long as the entries in column F (or since my formula was using the second criteria table the equivalent column J). 'blank' cells below the last entry are ignored, except of course if there's a blank cell and then another cell underneath with a value.

[Glenn Kennedy]

Richard... your formula works providing column F has the most entries. If, instead, 3 countries were selected and no cities, it would not work.

[Richard Buttrey]

Richard... your formula works providing column F has the most entries. If, instead, 3 countries were selected and no cities, it would not work.

Ah OK. I see the point you make.

That could presumably be addressed by modifying the COUNTA in the dynamic name definition and wrap two COUNTA formulae for columsn F & E in a MAX() function

[Levdan]

Thanks a lot for the input, this seems to be putting me on the right track!

However, I hit one really silly snag: I want to play around a little with the range definition, so I click up "Define name" but then when I click in the area where you define the cell range something that seems weird to me happens. Say I clicked right next to the equal sign. I now want to move over to the INDEX function and do some adjustments. But then Excel instead switches around in the sheet and completely removes the entire formula that you wrote, Glenn.

Surely you should be able to do minor adjustments to named ranges...?

[Richard Buttrey]

Hi,

Yes, I find that behaviour frustrating too. You need to hit F2 to put the window into Edit modebefore starting to adjust by moving left or eight in the name definition box.

It would be more helpful if Excel defaulted to edit mode in this situation.

[Glenn Kennedy]

As mentioned in Post 4... CTRL-F3.

[Glenn Kennedy]

Thats the spprosch that I took, refined at Post 5.

[Glenn Kennedy]

Yep. It's a real pain in the @$$ when that happens...I never remember about F2...