dsum? Sumif? other?

[rick-n]

I'm trying to use =dsum to build a list of data at sheet 1 from a range of data at sheet 3. After 20 years of 123 usage I just switched to Excel and can't seem to get the syntax down for this function. I'm beginning to wonder if I've even got the right function for what I want to do (dsum did this for me in 123). A little help anyone?

At Sheet3
A2:X2 contains column labels
Column A is labeled "code"
A3:A150 will contain an entered value between 1 and 50.
B3:B150 contains text
C3:W150 contains values; either entered or resulting from various functions.
Column X is labeled "totals"
X3:X150 contains values resulting from the function "sum this row from column C to column W.

Sheet1 will be a list with data returned from sheet 3. A1:A50 will contain consecutive values from 1 to 50.
Cloumn B of each row needs to return:
Sum the values at Sheet3!X3:X150 for ONLY THOSE ROWS in which the value(s) at Sheet3!A3:A150 equal the value entered in this row at column A.

I think I have the database and field syntaxes correct but I'm falling down on the criteria syntax-witness:
=dsum(Sheet3!A2:X150,"totals","code=A1"

Thanks for any help.

[daddylonglegs]

Hello Rick,

I'd use SUMIF, i.e. in sheet 1 B1

=SUMIF(Sheet3!A$3:A$150,A1,sheet3!X$3:X$150)

fill formula down column

[rick-n]

Hello Rick,

I'd use SUMIF, i.e. in sheet 1 B1

=SUMIF(Sheet3!A$3:A$150,A1,sheet3!X$3:X$150)

fill formula down column

I think I've failed to properly describe the data I want returned. The formula you describe got rid of the error message but doesn't return the data I'm looking for.

I want to return the sum of the values in the database field (sheet3!X$3:X$150) for the criteria "rows in the database at which the value(s) in column A equal the value at sheet1!A1.

Thanks again. I'm moving in the right direction.
want

[rick-n]

I think I've failed to properly describe the data I want returned. The formula you describe got rid of the error message but doesn't return the data I'm looking for...

I stand corrected. The formula does in fact return the sum of the values in the field provided all the cells in the database contain values. The convention is slightly different than 123 but nothing I can't work out without a bunch of =if(A=0,0,A). Thank you DLL.

[Carim]

Hi,

Would the following help

Please Login or Register  to view this content.

HTH
Carim

[rick-n]

Hi,

Would the following help

Please Login or Register  to view this content.

HTH
Carim

It doesn't seem to - but it does not return an error message.

[Carim]

Hi,

If you are dealing with text ...

Please Login or Register  to view this content.

HTH
Carim

[Maistrye]

The one catch I've found with the database functions like DSUM is that you need to have the conditions in actual cells on the sheet. (I've asked if there is a way around this... but I never did get a response)

So for DSUM's criteria, you'd need (for example) to have the column header in cell D1 and the value you're looking for in D2.

Then you pass D1:D2 as the criteria. If you want more conditions on the other columns, you can add them in E1:E2, etc. and pass the whole range as the criteria.

This causes problems with a list of values like you have, since putting the column header in between each row is troublesome at best. Until someone mentions a way to create the criteria list without having to actually reference existing cells, it will be a pain to use unless you are only doing it for one set of values.

Hope this makes some sense.

Scott

[rick-n]

The one catch I've found with the database functions like DSUM is that you need to have the conditions in actual cells on the sheet...This causes problems with a list of values like you have [and] is troublesome at best.
Scott

I made =SUMIF return what I wanted by moving the "sum range" coulmn 'X' from the far end of the data range to coulmn A (adjacent to the "range"column) and bumping the "range" column from A toB. The formula now reads:
=SUMIF(Sheet3!B$3:B$150,Sheet1!A1,Sheet3!A$3:A$150)
This results in the return of the desired data without regard to the abscence of values within the data range summed by the "sum range" cells.

Thanks for all the input.

[daddylonglegs]

Glad it worked for you rick but I don't really understand why moving the columns should make a difference, SUMIF should give the same result regardless.

I'm also not sure what the previous problem was. If A1 contains a number then you should just get the sum forr column X where column A equals that number, or did you have error values in the sum range?

[rick-n]

I'm also not sure what the previous problem was. If A1 contains a number then you should just get the sum forr column X where column A equals that number, or did you have error values in the sum range?

The previous problem was that=dsum apparently does not accomodate a 'logical' criteria; that is it won't return the sum of values in the database 'field'(column X) for a 'true' statement between the value of one cell (A1) and those values otherwise found elsewhere within in a range in the database (Column A). Or at least I couldn't get it done. I was hung up because 123's @dsum function allows for the logical criteria and I was convinced that the stmbling block was my lack of familiariy with Excel syntax.

I went and moved column A back to column X - you're right that it had no effect on =sumif. I guess the effect I discovered was upon some aspect of the functionality of =dsum. Thanks again for the help DLL.