DSUM : blank cell as a required criteria, can it be done?

[C M P]

The Scenario:

..........A................B...............C
1....HeaderA.....HeaderB.....Header C
2.......10................................3
3........2..................................
4........6................9................
5.......13.................................


The Question:

Is it possible to only sum the cells within A2:A5 in which the corresponding cells in column B and C are empty?
(The desired answer in this case being 2+13=15).

If this is possible, in what ways could it be most easily accomplished?

 

[mikerickson]

DSUM criteria ranges accept custom formulas.
Normaly, the first row of a criteria range holds the header name for the condition. If a cell in the top row of a criteria range is blank, it signals that the cell below holds a formula similar to those used for condidional formatting and validation.

=AND(B1="",C1="")

should do what you want.

Help system "criteria range" goes into this in greater detail.

 

[C M P]

My apologies for not fully understanding your reply. Perhaps this image can better clarify my question.

 

[Aladin Akyurek]

Book4
	A	B	C	D	E
1
2	FALSE	FALSE	TRUE
3
4
5	Date Debt Occurred	Debt Amount	Amount Paid	Date Paid	Date Account Closed
6	1/1/2007	10	10	1/2/2007
7	1/2/2007	20	20	1/3/2007
8	1/3/2007	30
9	1/4/2007	40	40	1/5/2007
10	1/5/2007	50	50	1/6/2007
11	1/6/2007	60
12	1/7/2007	70			2/22/2007
13	1/8/2007	80	80	1/8/2007
14	1/9/2007	90	90	1/9/2007
15			DSUM:		90
16			SUMPRODUCT:		90
17
Sheet1

Criteria range: A1:C1 empty.

A2, copied across to C2:

=C6=""

E15: with DSUM

=DSUM(B5:E14,1,A1:C2)

A different approach...

F15:

=SUMPRODUCT(B6:B14,--(C6:C14=""),--(D6:D14=""),--(E6:E14=""))

DSUM would execute faster, while the setup for it would require some effort...

 

[mikerickson]

=dsum(a5:e14,"debt amount",a1:e2) with "=0" in c2 , blanks in other row 2 cells.