Ignore #N/A in DSum formula

[davedec]

Hi,

This is my first post so hopefully it is done correctly.

Is there a way of excluding #N/A figures using DSum.

There is data in columns (date sorted) which are formula driven and uses NA() instead of zero so that only numbers > 0 are charted.

The book also has MTD figures using criteria to show MTD and YTD analysis, Dsum is used for these but I have not been able to find a solution for excluding #N/A from this formula

Current formula used is "=DSUM($H$1:$O$1000,A4,$E$2:$F$3)"

Any solutions to this?

Regards

David

 

[DeezNuts]

=IFERROR(DSUM($H$1:$O$1000,A4,$E$2:$F$3),"")

should do it

 

[davedec]

Hi William,

Thanks for the reply. What I was looking for is DSum to ignore the #N/A in the relevant column in the dataset.

Cheers

Dave

 

[Aladin Akyurek]

Hi,

This is my first post so hopefully it is done correctly.

Is there a way of excluding #N/A figures using DSum.

There is data in columns (date sorted) which are formula driven and uses NA() instead of zero so that only numbers > 0 are charted.

The book also has MTD figures using criteria to show MTD and YTD analysis, Dsum is used for these but I have not been able to find a solution for excluding #N/A from this formula

Current formula used is "=DSUM($H$1:$O$1000,A4,$E$2:$F$3)"

Any solutions to this?

Regards

David

Which column are you summing?

 

[davedec]

Hi Aladin,

I am summing Column J.

Cheers

Dave

 

[AhoyNC]

Try adding does not equal #N/A in your criteria field.

See example below.

Excel Workbook
	A	B	C	D	E	F
1	Data	Sum		Data	Sum	Sum
2	Apples	1		Apples	>3	#N/A
3	pear	3
4	Apples	#N/A		Sum of	Apples >3
5	Apples	6		16
6	orange	4
7	pear	5
8	Apples	10
Sheet

 

[Aladin Akyurek]

Hi Aladin,

I am summing Column J.

Cheers

Dave

Extend the criteria range with G2:G3 where

G2 is left empty

G3 has the following formula either:

=OR(J2<0,J2>0)

Or:

=J2 < 9.99999999999999E+307

The DSUM formula now becomes:

=DSUM($H$1:$O$1000,A4,$E$2:$G$3)

 

[davedec]

Cheers DeezNuts / Aladin / AhoyNC - appreciate your responses.

I found using the "=sumifs" formula with both date criteria and ">0" (criteria also) to exclude the #N/A but still sum the numbers in the column worked really well.

Many ways to skin a cat hey

Best

Dave

 

[Aladin Akyurek]

Cheers DeezNuts / Aladin / AhoyNC - appreciate your responses.

I found using the "=sumifs" formula with both date criteria and ">0" (criteria also) to exclude the #N/A but still sum the numbers in the column worked really well.

Many ways to skin a cat hey

Best

Dave

So, you gave up on DSUM in favor of SUMIFS? As shown to you, DSUM can be made to avoid #N/A's.

With SUMIFS...

Either...

=SUM(SUMIFS(SumRange,SumRange,{"<0",">0"},...))

Or...

=SUMIFS(SumRange,SumRange,"<"&9.99E+307,...)