Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
AA
 
Posts: n/a
Default Criteria problems in the DSUM function

I have an excel sheet with three worksheets.
1. Database from which the final values is looked from ("NetData")
2. Database that provides the IDs for lookup ("Template")
3. Final sheet where the value is posted

I am performing following ways for DSUM to work
=DSUM(NetData!A1:K313,"LENGTH",NetData!L1:L2)
=DSUM(NetData!A1:K313,"LENGTH","""AASEGNO"&"="&Tem plate!V5&"""")
=DSUM(NetData!A1:K313,"LENGTH",NetData!A1=Template !V5)
=DSUM(NetData!A1:K313,"LENGTH","AASEGNO"=Template! V5)

The variable "Length" is being summed up. The criteria - NetData!L1:L2 works
where the I have manually entered what I have being trying to do in the three
other ways where I make the value NetData!L2 as to be a variable having a
value that is contained in Template sheet, the only difference is that in
first one I can only enter one value Template!V5. Can anybody help with why
the last three ways of setting up the criteria is wrong along with the
correction?

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Peo Sjoblom
 
Posts: n/a
Default Criteria problems in the DSUM function

If you want to use the contents in Template!V5 to get a variable you can use
INDIRECT but how you apply it depends on what you want to type into V5, if
you want to type NetData!A1 into Template!V5 you can use

=DSUM(NetData!A1:K313,INDIRECT(Template!V5),NetDat a!L1:L2)

if you want to type in the criteria range into Template!V5 you can use

=DSUM(NetData!A1:K313,"LENGTH",INDIRECT(Template!V 5))

with NetData!L1:L2 in V5 it will return the same as in your first example

I assumed you have a header in A1, anyway INDIRECT would be the way to g

--
Regards,

Peo Sjoblom

(No private emails please)


"AA" wrote in message
...
I have an excel sheet with three worksheets.
1. Database from which the final values is looked from ("NetData")
2. Database that provides the IDs for lookup ("Template")
3. Final sheet where the value is posted

I am performing following ways for DSUM to work
=DSUM(NetData!A1:K313,"LENGTH",NetData!L1:L2)
=DSUM(NetData!A1:K313,"LENGTH","""AASEGNO"&"="&Tem plate!V5&"""")
=DSUM(NetData!A1:K313,"LENGTH",NetData!A1=Template !V5)
=DSUM(NetData!A1:K313,"LENGTH","AASEGNO"=Template! V5)

The variable "Length" is being summed up. The criteria - NetData!L1:L2
works
where the I have manually entered what I have being trying to do in the
three
other ways where I make the value NetData!L2 as to be a variable having a
value that is contained in Template sheet, the only difference is that in
first one I can only enter one value Template!V5. Can anybody help with
why
the last three ways of setting up the criteria is wrong along with the
correction?


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
AA
 
Posts: n/a
Default Criteria problems in the DSUM function

Hi Peo,

It didn't answer my question. I think you got it all wrong.
The problem was not in the "LENGTH". I want to sum up the column with the
field name "LENGTH" but the problem lies in defining the criteria. I don't
know how to write a criteria that one of the field name (here "AASEGNO") is
equal to the some value from other sheet (here Template!V5). The four ways
that I was trying to get it, only the first way works because I specify the
cells NetData!L1 as AASEGNO and NetData!L2 as MTH101 which is there occurs
three times in the database NetData!A1:K313).
Can anybody help me out constructing this criteria in which one of the
variable "AASEGNO" from the database NetData!A1:K313 is equal to some other
linked cell value at Template!V5?

AA

"Peo Sjoblom" wrote:

If you want to use the contents in Template!V5 to get a variable you can use
INDIRECT but how you apply it depends on what you want to type into V5, if
you want to type NetData!A1 into Template!V5 you can use

=DSUM(NetData!A1:K313,INDIRECT(Template!V5),NetDat a!L1:L2)

if you want to type in the criteria range into Template!V5 you can use

=DSUM(NetData!A1:K313,"LENGTH",INDIRECT(Template!V 5))

with NetData!L1:L2 in V5 it will return the same as in your first example

I assumed you have a header in A1, anyway INDIRECT would be the way to g

--
Regards,

Peo Sjoblom

(No private emails please)


"AA" wrote in message
...
I have an excel sheet with three worksheets.
1. Database from which the final values is looked from ("NetData")
2. Database that provides the IDs for lookup ("Template")
3. Final sheet where the value is posted

I am performing following ways for DSUM to work
=DSUM(NetData!A1:K313,"LENGTH",NetData!L1:L2)
=DSUM(NetData!A1:K313,"LENGTH","""AASEGNO"&"="&Tem plate!V5&"""")
=DSUM(NetData!A1:K313,"LENGTH",NetData!A1=Template !V5)
=DSUM(NetData!A1:K313,"LENGTH","AASEGNO"=Template! V5)

The variable "Length" is being summed up. The criteria - NetData!L1:L2
works
where the I have manually entered what I have being trying to do in the
three
other ways where I make the value NetData!L2 as to be a variable having a
value that is contained in Template sheet, the only difference is that in
first one I can only enter one value Template!V5. Can anybody help with
why
the last three ways of setting up the criteria is wrong along with the
correction?



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Peo Sjoblom
 
Posts: n/a
Default Criteria problems in the DSUM function

OK, if it would work with AASEGNO in L1 and criteria in L2 you want to know
how you can replace the criteria
and use V5 instead? If so leave L1 blank (yes blank) and put a formula in L2

=B2=Template!$V$5

where B2 is the first data cell in the column with header AASEGNO, note that
you need to use relative reference for B2 and absolute for the criteria in
the template sheet

--
Regards,

Peo Sjoblom

(No private emails please)


"AA" wrote in message
...
Hi Peo,

It didn't answer my question. I think you got it all wrong.
The problem was not in the "LENGTH". I want to sum up the column with the
field name "LENGTH" but the problem lies in defining the criteria. I don't
know how to write a criteria that one of the field name (here "AASEGNO")
is
equal to the some value from other sheet (here Template!V5). The four ways
that I was trying to get it, only the first way works because I specify
the
cells NetData!L1 as AASEGNO and NetData!L2 as MTH101 which is there occurs
three times in the database NetData!A1:K313).
Can anybody help me out constructing this criteria in which one of the
variable "AASEGNO" from the database NetData!A1:K313 is equal to some
other
linked cell value at Template!V5?

AA

"Peo Sjoblom" wrote:

If you want to use the contents in Template!V5 to get a variable you can
use
INDIRECT but how you apply it depends on what you want to type into V5,
if
you want to type NetData!A1 into Template!V5 you can use

=DSUM(NetData!A1:K313,INDIRECT(Template!V5),NetDat a!L1:L2)

if you want to type in the criteria range into Template!V5 you can use

=DSUM(NetData!A1:K313,"LENGTH",INDIRECT(Template!V 5))

with NetData!L1:L2 in V5 it will return the same as in your first example

I assumed you have a header in A1, anyway INDIRECT would be the way to g

--
Regards,

Peo Sjoblom

(No private emails please)


"AA" wrote in message
...
I have an excel sheet with three worksheets.
1. Database from which the final values is looked from ("NetData")
2. Database that provides the IDs for lookup ("Template")
3. Final sheet where the value is posted

I am performing following ways for DSUM to work
=DSUM(NetData!A1:K313,"LENGTH",NetData!L1:L2)
=DSUM(NetData!A1:K313,"LENGTH","""AASEGNO"&"="&Tem plate!V5&"""")
=DSUM(NetData!A1:K313,"LENGTH",NetData!A1=Template !V5)
=DSUM(NetData!A1:K313,"LENGTH","AASEGNO"=Template! V5)

The variable "Length" is being summed up. The criteria - NetData!L1:L2
works
where the I have manually entered what I have being trying to do in the
three
other ways where I make the value NetData!L2 as to be a variable having
a
value that is contained in Template sheet, the only difference is that
in
first one I can only enter one value Template!V5. Can anybody help with
why
the last three ways of setting up the criteria is wrong along with the
correction?




  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
AA
 
Posts: n/a
Default Criteria problems in the DSUM function

Dude I don't think it really answered my question.
I'll try to explain again:
If I have the value in cell NetData!L1 as AASEGNO and NetData!L2 as
=Template!V5
then in cell say Junk!A5 I can write the value be equal to following
=DSUM(NetData!A1:K313,"LENGTH",NetData!L1:L2)

Now in cell Junk!A6 I want something I do the same formula value
=DSUM(NetData!A1:K313,"LENGTH",NetData!L1:L2) but now the criteria should
be differently coded. So I want the value in NetData!L1 as AASEGNO (same as
previous) but in NetData!L2 as =Template!V6 (note the change from V5 to V6).

The way in which you are suggesting would not work.
I would re-phrase my question again:
How should I specify the criteria in a DSUM function in a case explained as
above?
This should be prettly explanable from the ways I have been struggling as
follows:
Case 1: Works Fine
=DSUM(NetData!A1:K313,"LENGTH",NetData!L1:L2)
Cases 2, 3 and 4: Doesn't work at all
=DSUM(NetData!A1:K313,"LENGTH","""AASEGNO"&"="&Tem plate!V5&"""")
=DSUM(NetData!A1:K313,"LENGTH",NetData!A1=Template !V5)
=DSUM(NetData!A1:K313,"LENGTH","AASEGNO"=Template! V5)

Why is that I can't specify the criteria as in cases 2, 3 and 4 above? There
got to be some point that I am missing?




I am performing following ways for DSUM to work
=DSUM(NetData!A1:K313,"LENGTH",NetData!L1:L2)
=DSUM(NetData!A1:K313,"LENGTH","""AASEGNO"&"="&Tem plate!V5&"""")
=DSUM(NetData!A1:K313,"LENGTH",NetData!A1=Template !V5)
=DSUM(NetData!A1:K313,"LENGTH","AASEGNO"=Template! V5)

"Peo Sjoblom" wrote:

OK, if it would work with AASEGNO in L1 and criteria in L2 you want to know
how you can replace the criteria
and use V5 instead? If so leave L1 blank (yes blank) and put a formula in L2

=B2=Template!$V$5

where B2 is the first data cell in the column with header AASEGNO, note that
you need to use relative reference for B2 and absolute for the criteria in
the template sheet

--
Regards,

Peo Sjoblom

(No private emails please)


"AA" wrote in message
...
Hi Peo,

It didn't answer my question. I think you got it all wrong.
The problem was not in the "LENGTH". I want to sum up the column with the
field name "LENGTH" but the problem lies in defining the criteria. I don't
know how to write a criteria that one of the field name (here "AASEGNO")
is
equal to the some value from other sheet (here Template!V5). The four ways
that I was trying to get it, only the first way works because I specify
the
cells NetData!L1 as AASEGNO and NetData!L2 as MTH101 which is there occurs
three times in the database NetData!A1:K313).
Can anybody help me out constructing this criteria in which one of the
variable "AASEGNO" from the database NetData!A1:K313 is equal to some
other
linked cell value at Template!V5?

AA

"Peo Sjoblom" wrote:

If you want to use the contents in Template!V5 to get a variable you can
use
INDIRECT but how you apply it depends on what you want to type into V5,
if
you want to type NetData!A1 into Template!V5 you can use

=DSUM(NetData!A1:K313,INDIRECT(Template!V5),NetDat a!L1:L2)

if you want to type in the criteria range into Template!V5 you can use

=DSUM(NetData!A1:K313,"LENGTH",INDIRECT(Template!V 5))

with NetData!L1:L2 in V5 it will return the same as in your first example

I assumed you have a header in A1, anyway INDIRECT would be the way to g

--
Regards,

Peo Sjoblom

(No private emails please)


"AA" wrote in message
...
I have an excel sheet with three worksheets.
1. Database from which the final values is looked from ("NetData")
2. Database that provides the IDs for lookup ("Template")
3. Final sheet where the value is posted

I am performing following ways for DSUM to work
=DSUM(NetData!A1:K313,"LENGTH",NetData!L1:L2)
=DSUM(NetData!A1:K313,"LENGTH","""AASEGNO"&"="&Tem plate!V5&"""")
=DSUM(NetData!A1:K313,"LENGTH",NetData!A1=Template !V5)
=DSUM(NetData!A1:K313,"LENGTH","AASEGNO"=Template! V5)

The variable "Length" is being summed up. The criteria - NetData!L1:L2
works
where the I have manually entered what I have being trying to do in the
three
other ways where I make the value NetData!L2 as to be a variable having
a
value that is contained in Template sheet, the only difference is that
in
first one I can only enter one value Template!V5. Can anybody help with
why
the last three ways of setting up the criteria is wrong along with the
correction?







  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
AA
 
Posts: n/a
Default Criteria problems in the DSUM function

I am still waiting for somebody to provide a solution to the criteria in DSUM
function. Can somebody please help?

"AA" wrote:

Dude I don't think it really answered my question.
I'll try to explain again:
If I have the value in cell NetData!L1 as AASEGNO and NetData!L2 as
=Template!V5
then in cell say Junk!A5 I can write the value be equal to following
=DSUM(NetData!A1:K313,"LENGTH",NetData!L1:L2)

Now in cell Junk!A6 I want something I do the same formula value
=DSUM(NetData!A1:K313,"LENGTH",NetData!L1:L2) but now the criteria should
be differently coded. So I want the value in NetData!L1 as AASEGNO (same as
previous) but in NetData!L2 as =Template!V6 (note the change from V5 to V6).

The way in which you are suggesting would not work.
I would re-phrase my question again:
How should I specify the criteria in a DSUM function in a case explained as
above?
This should be prettly explanable from the ways I have been struggling as
follows:
Case 1: Works Fine
=DSUM(NetData!A1:K313,"LENGTH",NetData!L1:L2)
Cases 2, 3 and 4: Doesn't work at all
=DSUM(NetData!A1:K313,"LENGTH","""AASEGNO"&"="&Tem plate!V5&"""")
=DSUM(NetData!A1:K313,"LENGTH",NetData!A1=Template !V5)
=DSUM(NetData!A1:K313,"LENGTH","AASEGNO"=Template! V5)

Why is that I can't specify the criteria as in cases 2, 3 and 4 above? There
got to be some point that I am missing?




I am performing following ways for DSUM to work
=DSUM(NetData!A1:K313,"LENGTH",NetData!L1:L2)
=DSUM(NetData!A1:K313,"LENGTH","""AASEGNO"&"="&Tem plate!V5&"""")
=DSUM(NetData!A1:K313,"LENGTH",NetData!A1=Template !V5)
=DSUM(NetData!A1:K313,"LENGTH","AASEGNO"=Template! V5)

"Peo Sjoblom" wrote:

OK, if it would work with AASEGNO in L1 and criteria in L2 you want to know
how you can replace the criteria
and use V5 instead? If so leave L1 blank (yes blank) and put a formula in L2

=B2=Template!$V$5

where B2 is the first data cell in the column with header AASEGNO, note that
you need to use relative reference for B2 and absolute for the criteria in
the template sheet

--
Regards,

Peo Sjoblom

(No private emails please)


"AA" wrote in message
...
Hi Peo,

It didn't answer my question. I think you got it all wrong.
The problem was not in the "LENGTH". I want to sum up the column with the
field name "LENGTH" but the problem lies in defining the criteria. I don't
know how to write a criteria that one of the field name (here "AASEGNO")
is
equal to the some value from other sheet (here Template!V5). The four ways
that I was trying to get it, only the first way works because I specify
the
cells NetData!L1 as AASEGNO and NetData!L2 as MTH101 which is there occurs
three times in the database NetData!A1:K313).
Can anybody help me out constructing this criteria in which one of the
variable "AASEGNO" from the database NetData!A1:K313 is equal to some
other
linked cell value at Template!V5?

AA

"Peo Sjoblom" wrote:

If you want to use the contents in Template!V5 to get a variable you can
use
INDIRECT but how you apply it depends on what you want to type into V5,
if
you want to type NetData!A1 into Template!V5 you can use

=DSUM(NetData!A1:K313,INDIRECT(Template!V5),NetDat a!L1:L2)

if you want to type in the criteria range into Template!V5 you can use

=DSUM(NetData!A1:K313,"LENGTH",INDIRECT(Template!V 5))

with NetData!L1:L2 in V5 it will return the same as in your first example

I assumed you have a header in A1, anyway INDIRECT would be the way to g

--
Regards,

Peo Sjoblom

(No private emails please)


"AA" wrote in message
...
I have an excel sheet with three worksheets.
1. Database from which the final values is looked from ("NetData")
2. Database that provides the IDs for lookup ("Template")
3. Final sheet where the value is posted

I am performing following ways for DSUM to work
=DSUM(NetData!A1:K313,"LENGTH",NetData!L1:L2)
=DSUM(NetData!A1:K313,"LENGTH","""AASEGNO"&"="&Tem plate!V5&"""")
=DSUM(NetData!A1:K313,"LENGTH",NetData!A1=Template !V5)
=DSUM(NetData!A1:K313,"LENGTH","AASEGNO"=Template! V5)

The variable "Length" is being summed up. The criteria - NetData!L1:L2
works
where the I have manually entered what I have being trying to do in the
three
other ways where I make the value NetData!L2 as to be a variable having
a
value that is contained in Template sheet, the only difference is that
in
first one I can only enter one value Template!V5. Can anybody help with
why
the last three ways of setting up the criteria is wrong along with the
correction?





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
add DSum function that functions horizontally. RHusmann Excel Worksheet Functions 1 August 11th 05 02:48 AM
Function to look up largest value whose record meets criteria? Alison Excel Worksheet Functions 5 August 3rd 05 09:13 PM
Date & Time mully New Users to Excel 4 May 23rd 05 11:56 AM
how do I isolate only one row in the criteria for the dsum functi. lxnv Excel Worksheet Functions 1 March 22nd 05 03:21 PM
DSum function Mark Excel Worksheet Functions 2 November 17th 04 03:47 PM


All times are GMT +1. The time now is 09:40 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"