Criteria problems in the DSUM function

Discussion in 'Microsoft Excel Worksheet Functions' started by Guest, Jan 6, 2006.

  1. Guest

    Guest Guest

    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"&"="&Template!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?
     
    Guest, Jan 6, 2006
    #1
    1. Advertisements

  2. Guest

    Peo Sjoblom Guest

    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),NetData!L1:L2)

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

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

    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
    news:D...
    >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"&"="&Template!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?
    >
     
    Peo Sjoblom, Jan 6, 2006
    #2
    1. Advertisements

  3. Guest

    Guest Guest

    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),NetData!L1:L2)
    >
    > if you want to type in the criteria range into Template!V5 you can use
    >
    > =DSUM(NetData!A1:K313,"LENGTH",INDIRECT(Template!V5))
    >
    > 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
    > news:D...
    > >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"&"="&Template!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?
    > >

    >
    >
     
    Guest, Jan 6, 2006
    #3
  4. Guest

    Peo Sjoblom Guest

    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
    news:...
    > 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),NetData!L1:L2)
    >>
    >> if you want to type in the criteria range into Template!V5 you can use
    >>
    >> =DSUM(NetData!A1:K313,"LENGTH",INDIRECT(Template!V5))
    >>
    >> 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
    >> news:D...
    >> >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"&"="&Template!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?
    >> >

    >>
    >>
     
    Peo Sjoblom, Jan 7, 2006
    #4
  5. Guest

    Guest Guest

    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"&"="&Template!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"&"="&Template!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
    > news:...
    > > 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),NetData!L1:L2)
    > >>
    > >> if you want to type in the criteria range into Template!V5 you can use
    > >>
    > >> =DSUM(NetData!A1:K313,"LENGTH",INDIRECT(Template!V5))
    > >>
    > >> 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
    > >> news:D...
    > >> >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"&"="&Template!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?
    > >> >
    > >>
    > >>

    >
    >
     
    Guest, Jan 7, 2006
    #5
  6. Guest

    Guest Guest

    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"&"="&Template!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"&"="&Template!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
    > > news:...
    > > > 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),NetData!L1:L2)
    > > >>
    > > >> if you want to type in the criteria range into Template!V5 you can use
    > > >>
    > > >> =DSUM(NetData!A1:K313,"LENGTH",INDIRECT(Template!V5))
    > > >>
    > > >> 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
    > > >> news:D...
    > > >> >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"&"="&Template!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?
    > > >> >
    > > >>
    > > >>

    > >
    > >
     
    Guest, Jan 10, 2006
    #6
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Lance

    almost DSUM, but is there another database - criteria funcion?

    Lance, Sep 15, 2003, in forum: Microsoft Excel Worksheet Functions
    Replies:
    0
    Views:
    146
    Lance
    Sep 15, 2003
  2. Dianne

    Re: DCOUNT & DSUM w/ multiple criteria

    Dianne, Sep 25, 2003, in forum: Microsoft Excel Worksheet Functions
    Replies:
    1
    Views:
    298
    Onhimesa
    Sep 30, 2003
  3. Dave

    Alternative to calc differences of DSUM functions (with 5 or 6 criteria)

    Dave, Mar 26, 2004, in forum: Microsoft Excel Worksheet Functions
    Replies:
    3
    Views:
    235
    Harlan Grove
    Mar 29, 2004
  4. Guest

    DSUM Criteria and Excel Help

    Guest, Apr 30, 2004, in forum: Microsoft Excel Worksheet Functions
    Replies:
    3
    Views:
    2,520
    Harlan Grove
    Apr 30, 2004
  5. Guest

    How do I add a second criteria to the SUMIF or DSUM function?

    Guest, Jan 12, 2006, in forum: Microsoft Excel Worksheet Functions
    Replies:
    2
    Views:
    6,584
    John Michl
    Jan 12, 2006
Loading...

Share This Page