+ Reply to Thread
Results 1 to 6 of 6

To calculate Column and Rows

  1. #1
    Forum Contributor
    Join Date
    06-25-2016
    Location
    india
    MS-Off Ver
    MS Office 365(Win 10) Home Ed 2016
    Posts
    122

    To calculate Column and Rows

    Hello Everybody,

    from attached sample sheet I am want to calculate and add rows and colomn and tally both, i am managing it by simple way, but looking to proper formula, my sheet is big and calculating manuallhy will be comursome.

    attached sheet can explain more

    Thankx and regards,

    jimmiboy
    Attached Files Attached Files

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    20,929

    Re: To calculate Column and Rows

    It is really unclear what your question is so I am going to guess.

    Your "Rate" data is strings. You need to break this into two columns so one column is numbers.

    The Total Order should use SUM.

    Your math in Total Order Cost doesn't make sense. The top number in the division seems to be the Rate but the bottom number seems pretty random.

    N
    6
    Total Order Cost
    7
    =(50/1000)*M7
    8
    =(35/100)*M8
    9
    =(25/100)*M9
    10
    =(40/12)*M10
    11
    =M11*25

    I have left the bottom number in place for you to figure out but the top number is now the Rate. This formula can be made more automatic if you explain where the bottom number comes from.
    Attached Files Attached Files
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Forum Contributor
    Join Date
    06-25-2016
    Location
    india
    MS-Off Ver
    MS Office 365(Win 10) Home Ed 2016
    Posts
    122

    Re: To calculate Column and Rows

    Yes, I can change Rate colomn, I can change, but main query is how to calculate individual cost ????

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,909

    Re: To calculate Column and Rows

    See attached

    in K12

    =SUMPRODUCT((K7:K11)*($H7:$H11)/($J7:$J11))
    Attached Files Attached Files
    Last edited by JohnTopley; 05-31-2022 at 11:36 AM.
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  5. #5
    Forum Contributor
    Join Date
    06-25-2016
    Location
    india
    MS-Off Ver
    MS Office 365(Win 10) Home Ed 2016
    Posts
    122

    Re: To calculate Column and Rows

    John,
    thkx yr formula works perfectly, howere I am trying to apply other way where its not work work can you pl advice
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,909

    Re: To calculate Column and Rows

    It will not work as you mutiplying columns (bold red) by rows ; see attached as per original

    =SUMPRODUCT((H4:L4)*($D3:$D7)/($E$3:$E$7))

    If you want the "new" layout then use

    =SUMPRODUCT(($H4:$L4)*($H$3:$L$3=$C$3:$C$7)*($D$3:$D$7)/($E$3:$E$7))
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Calculate Across multiple Rows Depending on Column Content - end Result Date
    By Tybudd in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-27-2021, 10:11 AM
  2. [SOLVED] Calculate based on combination of multiple rows & column values
    By cologg in forum Excel General
    Replies: 3
    Last Post: 01-28-2021, 03:15 AM
  3. Calculate Average Time between Two Rows in a Range of Many Non-Consectuive Rows
    By aferoz in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-13-2014, 11:51 PM
  4. Calculate Average Time between Two Rows in a Range of Many Non-Consectuive Rows
    By aferoz in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-23-2014, 08:47 PM
  5. Replies: 0
    Last Post: 04-15-2014, 12:02 PM
  6. Calculated column to only calculate populated rows?
    By brianlg in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-28-2013, 09:36 PM
  7. VBA: calculate yield every 2 rows of 1 column in excel
    By zeno1 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-22-2012, 12:13 PM

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