+ Reply to Thread
Results 1 to 17 of 17

sum - region wise

  1. #1
    Forum Contributor
    Join Date
    10-24-2006
    Location
    INDIA
    MS-Off Ver
    office 2010
    Posts
    190

    sum - region wise

    Hi

    I have data with different fields like product name, region and year wise

    Data as follows
    In A1 column - All the product
    In B1 column 4 regions (north, south, east and west)
    In Y column 2006 totals product wise
    In Z column 2007 totals product wise

    *total projects are divided into 4 regions,

    Now I want to calculate product wise region wise total for 2006 year and so on
    (In simple way I want to calculate total sale for x product in north region for 2006)

    I think sumif function will work in this case, but I can't apply it correctly.
    Can any one help me?
    Thank you in advance - Sagar

  2. #2
    Forum Expert Carim's Avatar
    Join Date
    04-07-2006
    Posts
    3,993
    Hi,

    With a complete worksheet like yours, I would recommend for the best flexibility to create a pivot table ...

    HTH
    Carim

  3. #3
    Forum Contributor
    Join Date
    10-24-2006
    Location
    INDIA
    MS-Off Ver
    office 2010
    Posts
    190
    want to update with formula only, this output data agin i am using for some other calculations - sagar

  4. #4
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Post an example of ranges and sample data

    VBA Noob

  5. #5
    Forum Contributor
    Join Date
    10-24-2006
    Location
    INDIA
    MS-Off Ver
    office 2010
    Posts
    190

    sample data

    attached the sample data
    bye-sagar

  6. #6
    Forum Expert Carim's Avatar
    Join Date
    04-07-2006
    Posts
    3,993
    Attached ... ???

  7. #7
    Forum Contributor
    Join Date
    10-24-2006
    Location
    INDIA
    MS-Off Ver
    office 2010
    Posts
    190

    count region wise

    Hi Carim

    I am not able to see any attachment, please resend it again

    thanks for your time - Sagar

  8. #8
    Forum Contributor
    Join Date
    10-24-2006
    Location
    INDIA
    MS-Off Ver
    office 2010
    Posts
    190
    Can any one please help me, with formula, urgent requirement please

    Thanks-Sagar

  9. #9
    Forum Contributor
    Join Date
    07-13-2006
    Posts
    400
    Quote Originally Posted by sagar
    attached the sample data
    bye-sagar
    there's no attachment
    that's what carim was referencing too

  10. #10
    Forum Contributor
    Join Date
    10-24-2006
    Location
    INDIA
    MS-Off Ver
    office 2010
    Posts
    190
    There is problem in attachment link, it shows "error on Page"
    copy and pasted the data

    Sorry for the inconvenience caused, please help...

    Product Name Region 2006 2007 2008
    a North 3.08 4.83 4.08
    a South 4.08 5.83 5.08
    d East 5.08 6.83 6.08
    b West 6.08 7.83 7.08
    b North 7.08 8.83 8.08
    b South 8.08 9.83 9.08
    d East 9.08 10.83 10.08
    b West 10.08 11.83 11.08
    b North 11.08 12.83 12.08
    b South 12.08 13.83 13.08
    a East 13.08 14.83 14.08
    a West 14.08 15.83 15.08
    c North 15.08 16.83 16.08
    c South 16.08 17.83 17.08
    b East 17.08 18.83 18.08
    c West 18.08 19.83 19.08
    d North 19.08 20.83 20.08
    c South 20.08 21.83 21.08
    c East 21.08 22.83 22.08
    d West 22.08 23.83 23.08
    d North 23.08 24.83 24.08
    b South 24.08 25.83 25.08
    c East 25.08 26.83 26.08
    d West 26.08 27.83 27.08


    2006 2007
    North south East west North south East west
    a 3.08 4.08 13.08 14.08
    b 18.16 44.24 17.08 16.16
    c 15.08 36.16 46.16 18.08
    d 42.16 0 14.16 48.16
    Total 78.48 84.48 90.48 96.48

    Added manual totals for 2006, I want same way with formula

  11. #11
    Forum Contributor
    Join Date
    07-13-2006
    Posts
    400
    Quote Originally Posted by sagar
    There is problem in attachment link, it shows "error on Page"
    copy and pasted the data

    Sorry for the inconvenience caused, please help...

    Product Name Region 2006 2007 2008
    a North 3.08 4.83 4.08
    a South 4.08 5.83 5.08
    d East 5.08 6.83 6.08
    b West 6.08 7.83 7.08
    b North 7.08 8.83 8.08
    b South 8.08 9.83 9.08
    d East 9.08 10.83 10.08
    b West 10.08 11.83 11.08
    b North 11.08 12.83 12.08
    b South 12.08 13.83 13.08
    a East 13.08 14.83 14.08
    a West 14.08 15.83 15.08
    c North 15.08 16.83 16.08
    c South 16.08 17.83 17.08
    b East 17.08 18.83 18.08
    c West 18.08 19.83 19.08
    d North 19.08 20.83 20.08
    c South 20.08 21.83 21.08
    c East 21.08 22.83 22.08
    d West 22.08 23.83 23.08
    d North 23.08 24.83 24.08
    b South 24.08 25.83 25.08
    c East 25.08 26.83 26.08
    d West 26.08 27.83 27.08


    2006 2007
    North south East west North south East west
    a 3.08 4.08 13.08 14.08
    b 18.16 44.24 17.08 16.16
    c 15.08 36.16 46.16 18.08
    d 42.16 0 14.16 48.16
    Total 78.48 84.48 90.48 96.48

    Added manual totals for 2006, I want same way with formula
    =sumproduct(--($A$1:$A$100="a"),--($B$1:$B$100="north"),($C$1:$C$100))

    from how I read your spreadsheet that will sum all product a in the north in 2006 (assuming 100 rows)

  12. #12
    Forum Contributor
    Join Date
    10-24-2006
    Location
    INDIA
    MS-Off Ver
    office 2010
    Posts
    190
    Hi MDubbelboer,

    Sorry I am not getting any value with that formula, the way you are looking the values are correct, please help me or send me the attached file to my mail id vidyasagar_cm@yahoo.com or please guide me how to proceed

    Best Regards
    Sagar

  13. #13
    Forum Contributor
    Join Date
    07-13-2006
    Posts
    400
    Quote Originally Posted by sagar
    Hi MDubbelboer,

    Sorry I am not getting any value with that formula, the way you are looking the values are correct, please help me or send me the attached file to my mail id vidyasagar_cm@yahoo.com or please guide me how to proceed

    Best Regards
    Sagar
    attachment
    Attached Files Attached Files

  14. #14
    Forum Contributor
    Join Date
    10-24-2006
    Location
    INDIA
    MS-Off Ver
    office 2010
    Posts
    190
    Hi MDubbelboer,

    Thanks a lot for your timely help. really great help for me in this regard

    sagar

  15. #15
    Forum Contributor
    Join Date
    07-13-2006
    Posts
    400
    Quote Originally Posted by sagar
    Hi MDubbelboer,

    Thanks a lot for your timely help. really great help for me in this regard

    sagar
    glad i could be of help. if you are having problems modifying that in anyway to suit your file just ask away and i'm sure you'll get a quick response

  16. #16
    Forum Contributor
    Join Date
    10-24-2006
    Location
    INDIA
    MS-Off Ver
    office 2010
    Posts
    190
    Hi MDubbelboer,

    =sumproduct(--($A$1:$A$100="a"),--($B$1:$B$100="north"),($C$1:$C$100))

    Please clarify why we are using"--" in formula,
    Sagar

  17. #17
    Forum Contributor
    Join Date
    07-13-2006
    Posts
    400
    Quote Originally Posted by sagar
    Hi MDubbelboer,

    =sumproduct(--($A$1:$A$100="a"),--($B$1:$B$100="north"),($C$1:$C$100))

    Please clarify why we are using"--" in formula,
    Sagar
    -- is actually acting like two minus symbols

    so essentially it's taking a binary condition "does the relevant cell from A1 to A1000 = "a", and if it does it converts the TRUE to a negative "-1" and then the second minus symbol converts it back to "+1" to ensure that when that criteria is met we are given the value of 1. instead of the value of true.
    without the minus minus you'd have True*True*count which usually gives excel a bit of troubles

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1