Dsum on condition

[shibulal]

I have the total of a column with the formula ---> =DSUM($A$7:$G$65000,"Proj",drill_it!A1:B2)- (A1:B2 is where I have defined the criteria & drill_it is sheet name).

But this formula calculate the total of all figures in column named "Proj" - including minus value. I just want to toal only the positive value in "Proj" column. Is it possible ?

 

[Hui]

Shibulal


Is it possible to upload a small sample of your data for us to look at?

Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook

 

[shibulal]

Hi,


My company security policy denies to get in to the link....

I appreciate your helping nature...


Hui, I am absolutely sorry.......

 

[Luke M]

Add that as another criteria for your function. E.g., in drill_it C1:C2, have this:

[pre]

Proj
>0

[/pre]

 

[shibulal]

Hi Luke,


I failed to plot it correctly.

in C1:C2, I've given


Proj

>0 ------> it didn't give the desired result.


then I gave


proj

="=>0" -------------> it gave 0 as value.

 

[Luke M]

shibulal,

I'm not sure what all fields and criteria you have listed, so I'll just post a possible example. With this layout:

[pre]

Month	Year	Proj		Month	Year	Proj
Jan	2009	-2		Mar	>=2010	>0
Feb	2009	8
Mar	2009	0
Jan	2010	13
Feb	2010	-6
Mar	2010	8
Jan	2010	3
Feb	2011	10
Mar	2011	-3
June	2011	-8

[/pre]
First 3 columns are the data table, next 3 are my criteria. Using this formula:

=DSUM(A1:C11,"Proj",D1:F2)

would return a value of 8. This is the sum of all March values for all the years greater than or equal to 2010, where the value in Proj is greater than 0. Does that help?

 

[shibulal]

Hi Luke,

Sorry for the delay....


Though I have made a mistake in giving the field name, your suggestion made me to rework on the criteria and it is being resolved.....


Thanks a lot, Luke !

 

[Luke M]

No worries, just glad I was able to help.