Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 23

Thread: totals

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

    Default

    Hallo! can u please help me on this? it's a kind of an urgent!

    I have 2 columns like following:
    A B
    opt. 100
    pess. 50
    opt. 150
    pess. 80
    Total

    I want to create a scenario where the total should be based on column A (for example in C1 i write "opt." Then the total should summarize only the cells in column B where in col. A is "opt.". Can u please help me with this? Thanks u very much

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Where the wild roses grow
    Posts
    285
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sure, try this:

    =SUMPRODUCT((A1:A65336="OPT")*(B1:D65336))


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

    Default

    Thanks for this, but i'm afraid i didn't make myself quite clear. What i want is to get the sum of "opt." when the cell C1 is "opt.". But if the cell C1 is "pes." then i get the sum of the "pes." cells. Hope that helps more

    On 2002-04-11 06:01, Joanna_gr wrote:
    Hallo! can u please help me on this? it's a kind of an urgent!

    I have 2 columns like following:
    A B
    opt. 100
    pess. 50
    opt. 150
    pess. 80
    Total

    I want to create a scenario where the total should be based on column A (for example in C1 i write "opt." Then the total should summarize only the cells in column B where in col. A is "opt.". Can u please help me with this? Thanks u very much

  4. #4
    Board Regular Steve Hartman's Avatar
    Join Date
    Feb 2002
    Location
    Houston,Texas
    Posts
    417
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    =SUMIF(A1:A65336,C1,B1:B65336) will work for all of column A and B. Adjust the ranges as needed.



    [ This Message was edited by: Steve Hartman on 2002-04-11 06:47 ]

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,657
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-11 06:03, Audiojoe wrote:
    Sure, try this:

    =SUMPRODUCT((A1:A65336="OPT")*(B1:D65336))
    Audiojoe,

    SUMPRODUCT is seldom needed for summing/counting in situations with a single condition/criterion. SUMIF and COUNTIF would be more efficient to use.

    Regards,

    Aladin

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

    Default

    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

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Where the wild roses grow
    Posts
    285
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Please accept my apologies



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

    Default

    THANK YOU! THANK YOU! THANK YOU! U r really very helpfull as always

    On 2002-04-11 06:18, Steve Hartman wrote:
    =SUMIF(A1:A65336,C1,B1:B65336) will work for all of column A and B. Adjust the ranges as needed.



    [ This Message was edited by: Steve Hartman on 2002-04-11 06:47 ]

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

    Default

    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??

  10. #10
    Board Regular Steve Hartman's Avatar
    Join Date
    Feb 2002
    Location
    Houston,Texas
    Posts
    417
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Are opt and pess the only possible entries in column A?

Some videos you may like

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
  •