DSUM method does not work with negative values

[marcvanderpeet12]

Guys,

I have the following Excel sheet (see attached).

In the tab "Overzicht" I have the following formula in cell D11: "=DSUM(Datum_afschrift_26aug!A1:I200;Datum_afschrift_26aug!E1;'query''s'!C14:J15)"

This should give me a sum of all values in "Datum_afschrift_26aug" fitting the query I outlined in 'query''s'!C14:J15.

It does not seem to work however. Because it does work with query's where I only fetch positive values my assumption is that the negative values are causing the problem.

Any thoughts on what goes wrong here?

 

[Asheesh]

Hi,

No, this has nothing to do with negative or positive values..if you scoot thorugh data carefully, you would notice that it is reading some cells as text data...

Suppose...in Cell E14 of tab "Datum_afschrift_26aug" remove the "," from "-903,73" or put period "." in place of "," and see what happens in cell D11 of Tab "Overzicht"

You will have the answer once you do that...

Hope this helps...

 

[NARAYANK991]

Hi ,

You can also improve the formula , as follows :
=IFERROR(DSUM(Datum_afschrift_26aug!$A$1:$I$93,Datum_afschrift_26aug!$E$1,OFFSET('query''s'!$C$1:$J$1,MATCH(B4,'query''s'!$J$1:$J$27,0)-2,,2)),0)

See the file.

Narayan