totals - Page 2
Manage your personal finances in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 23

Thread: totals

  1. #11
    Board Regular
    Join Date
    Feb 2002
    Location
    Athens Greece
    Posts
    149
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    opt - pess - real
    are all the options

    On 2002-04-11 07:50, Joanna_gr wrote:
    well, another one little thing!! a bit urgent too!

    What should i do if C1 = opt. and i want to get the sum of opt. & pess., but if C1 is pess. i need to get the sum of pess. only!!!

    (for example :
    C1= opt
    A / B
    opt / 50
    opt / 100
    pess / 50
    total 200
    If C1 = pess
    then total 50

    Can u please help to that also??

  2. #12
    Board Regular
    Join Date
    Feb 2002
    Location
    Athens Greece
    Posts
    149
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    can u help me with that too pleeeeease?


    On 2002-04-11 07:54, Joanna_gr wrote:
    opt - pess - real
    are all the options

    On 2002-04-11 07:50, Joanna_gr wrote:
    well, another one little thing!! a bit urgent too!

    What should i do if C1 = opt. and i want to get the sum of opt. & pess., but if C1 is pess. i need to get the sum of pess. only!!!

    (for example :
    C1= opt
    A / B
    opt / 50
    opt / 100
    pess / 50
    total 200
    If C1 = pess
    then total 50

    Can u please help to that also??

  3. #13
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,579
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi

    Not very elegant, but this seems to work. If opt. and pess. are the only entries in column A the formula could be shortened

    =IF(C1="pess.",SUMIF(A1:A65336,"pess.",B1:B65336),SUMIF(A1:A65336,"pess.",B1:B65336)+SUMIF(A1:A65336,"opt.",B1:B65336))

    regards
    Derek

  4. #14
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Well Joanna:
    If your data is limited to what you described above,
    1) your total for both opt and pess will be
    =sum(A1:A3)
    2) =sumif(A1:A3,C1,B1:B3)

    It looks too simple -- are you sure that's all you want?

  5. #15
    Board Regular
    Join Date
    Feb 2002
    Location
    Athens Greece
    Posts
    149
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Derek thanks, but i have 3 options:

    opt. / pes. / real how about in such case?

    On 2002-04-11 08:09, Derek wrote:
    Hi

    Not very elegant, but this seems to work. If opt. and pess. are the only entries in column A the formula could be shortened

    =IF(C1="pess.",SUMIF(A1:A65336,"pess.",B1:B65336),SUMIF(A1:A65336,"pess.",B1:B65336)+SUMIF(A1:A65336,"opt.",B1:B65336))

    regards
    Derek

  6. #16
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,579
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Joanna

    This will ignore everything in A except opt. and pess. Do you want it to sum real. if "real" is typed in C1 as well?

    regards
    Derek

  7. #17
    Board Regular
    Join Date
    Feb 2002
    Location
    Athens Greece
    Posts
    149
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Let me make it more clear!

    If the cell A1 says "Opt" I sum "Opt" & "Pess" & "real", if A1 says "Real" i sum "pess" & "real" and if A1 says "P" i sum only the "p" cells

  8. #18
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,772
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-04-11 08:25, Joanna_gr wrote:
    Let me make it more clear!

    If the cell A1 says "Opt" I sum "Opt" & "Pess" & "real", if A1 says "Real" i sum "pess" & "real" and if A1 says "P" i sum only the "p" cells
    Joanna,

    The way you specify the conditions is a bit unusual. So be it.

    =(C1="Opt")*(SUM(B1:B100))+(C1="Real")*(SUMIF(A1:A100,"Real",B1:B100)+SUMIF(A1:A100,"Press",B1:B100))+(C1="Press")*(SUMIF(A1:A100,"Press",B1:B100))

    where C1 houses the desired condition, will do what you want, given the set of possible conditions.

    Aladin

    [ This Message was edited by: Aladin Akyurek on 2002-04-11 08:42 ]

  9. #19
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Joanna:
    Following up on where Derek took us, if your item and qty information is in columns A and B for "opt", "pess", and "real", and your lookup value (criterion is in cell C1, then use the formula:

    =IF(C1="opt",SUMIF(A:A,"opt",B:B)+SUMIF(A:A,"pess",B:B)+SUMIF(A:A,"real",B:B),IF(C1="real",SUMIF(A:A,"pess",B:B)+SUMIF(A:A,"real",B:B),IF(C1="pess",SUMIF(A:A,"pess",B:B))))

    This is not elegant -- but it works. You may also want to explore using D-functions!
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  10. #20
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,772
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

      
    On 2002-04-11 06:29, Derek wrote:
    Hi Joanna

    You could try this CSE macro, change the range to suit your range and note that there is a dot after opt. and pess. so it must be identical in C1

    As this is an array macro you must hold down control+shift+enter to enter it (it will then automatically acquire curly braces around it)

    =IF(C1="opt.",SUMIF(A5:B10,"opt",B5:B10),SUMIF(A5:B10,"pess.",B5:B10))

    Hope this helps
    regards
    Derek
    Derek,

    I'm a bit surprised by your new terminolgy CSE macro and array macro. Why do you think an array-formula is a macro?

    Regards,

    Aladin

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com