DSUM Beginner

[ikslohap]

There are a zillion posts about DSUM, but I don't know how to find ones that answer my question without reading all zillion -- searching titles hasn't helped much.

I'm fair to middling at Excel, including VBA, but have never used any database functions up to now. I tried to put in =DSUM(PersonsRange,"AgeGroup","Under 35") and got a #VALUE error. Put the rangename in quotes too, same. Tried = signs in various ways with the criterion, same. What am I doing wrong?

Oh -- also tried using the column number in place of the "AgeGroup," same result.

[martindwilson]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[ikslohap]

Big Duh for me, I need DCount, not DSum. Sorry, all. But I'll be back if I can't get that one to work either.

LATER: Surprised to find DCount only works with numbers, and only tells you how many cells contain numbers, i.e., not how many contain 7 or 43 or whatever. Apparently there'sno built-in D-function to count occurrences of a specific value. Seems like an obvious thing. Anyhow, once I gave up on the D-functions, this took five minutes and does the job just fine:

'Following will count items in any column of specified Range that match a specified value
Public Function CountItems(varRange As Variant, varValue As Variant) As Integer
Dim myRange As Range, myCel As Object
Set myRange = varRange
For Each myCel In myRange
If myCel.Value = varValue Then CountItems = CountItems + 1
Next myCel
End Function

[AlKey]

Just like with regular function SUM and COUNT and COUNTA there are DSUM, DCOUNT to count numbers and DCOUNTA for text.

[martindwilson]

=DCOUNT(A4:E10,"Age",C1:C2) where c6:c10 contain ages c5 is labeled "age" and c1 also contains the label "age"
then any value put in c2 will be counted so put 20 and all values of 20 in c6:c10 will be counted
exactly the same as countif(c6:c10,20)
similarly for dcounta
and i cannot see the value of
'Following will count items in any column of specified Range that match a specified value

Please Login or Register  to view this content.

since excel already has that function built in eg
=countif(a1:e120,"x") or countif(a2:a100,a2)

[ikslohap]

I don't follow that first part at all, frankly,but countif() is exactly what I needed. Was sure Excel must have something like that, but was only looking in the Database functions.