dsum / specifiying the criteria in a constant, not in a referencearray?

[xirx]

Hi!

The 3rd parameter of the DSUM function...

DSUM(database,field,criteria)

.... is a range that specifies the criteria. E.g.,
the range A1:B2 might be used to select all males
older than 30 from a database:

A B
----- -----
1 sex age
2 male >30

This requires the criteria to be on a worksheet.
I would like to have it in the formula. Can I
use a constant instead of a range, here?

I tried...

=DSUM(database;weight;{"sex","age";"male",">30"})

....but this doesn't work.

Any pointers?

 

[Peo Sjoblom]

Use SUMPRODUCT instead

=SUMPRODUCT(--(INDEX(database,,1)="male"),--(INDEX(database,,2)>30),INDEX(da
tabase,,3))


will lookup male in first column of database where 2nd column is >30 and sum
3rd column

bonus is that you can use it on closed workbooks

if you know the ranges you don't need INDEX

=SUMPRODUCT(--(A4:A500="male"),--(B4:B500>30),C4:C500)

 

[xirx]

Thx, that's exactly what I am looking for. It works.
But I'd like to understand *why* it works...

First, according the online-help, SUMPROD is for
creating sums of factors, that is to perform mathematical
calculations. There is no explanation on how it can be
used for complex database lookups.

Second: According the online-help, INDEX(Range;row;col)
returns the value of index at the intersection of row and
col. But what's the semantic if I just leave out either
row or col? Probably some kind of "just that row" (or col).
I found that count(index(database;;1)) returns the number
of rows in column 1. And max(index(database;;2)) returns
the maximum number in the 2nd column. Hoever,
choose(1;index(database;;1)) does not return the first
value of column 1, but just #VALUE.

Third, what does the --(...) mean?

And finally, how did you get to know all this?

Thx, again. And I would really appriciate if you
could answer these questions.

 

[xirx]

Thx, that's exactly what I am looking for. It works.
But I'd like to understand *why* it works...

First, according the online-help, SUMPROD is for
creating sums of factors, that is to perform mathematical
calculations. There is no explanation on how it can be
used for complex database lookups.

Second: According the online-help, INDEX(Range;row;col)
returns the value of index at the intersection of row and
col. But what's the semantic if I just leave out either
row or col? Probably some kind of "just that row" (or col).
I found that count(index(database;;1)) returns the number
of rows in column 1. And max(index(database;;2)) returns
the maximum number in the 2nd column. Hoever,
choose(1;index(database;;1)) does not return the first
value of column 1, but just #VALUE.

Third, what does the --(...) mean?

And finally, how did you get to know all this?

Thx, again. And I would really appriciate if you
could answer these questions.

 

[xirx]

This is a SUMIF with multiple conditions. Is there
a way to have a COUNTIF with multiple conditions?