Results 1 to 8 of 8

Thread: Sumproduct with Multiple Rows and Columns
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Oct 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Sumproduct with Multiple Rows and Columns

    Hi guys,

    Although I've been reading for a number of years, this is my first post. Please bear with me if I do something incorrectly.

    I have a workbook that is basically taking invoices and rolling them up by date, truck number, and expense type. The problem is there can be multiple invoices in a month. I think I need to use the sumproduct formula but I'm not super familiar with it.

    I have a summary that looks like the table below but I need to see everything by month and YTD. I can still have the Truck and Type columns (brakes, tires, etc...). Any ideas on what I can do? Thank you!

    Invoice 123 Invoice 456 Invoice 789
    August August September
    Truck 1 Brakes 50
    Truck 2 Tires 100
    Truck 2 Maintenance 25 25 25
    Truck 3 Brakes 50
    Truck 4 Maintenance 25 25

  2. #2
    Board Regular James006's Avatar
    Join Date
    Apr 2009
    Posts
    3,587
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Sumproduct with Multiple Rows and Columns

    Hi,

    What is you expected result ... ???

  3. #3
    New Member
    Join Date
    Oct 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sumproduct with Multiple Rows and Columns

    I need my final result to look like this:


    August YTD
    Truck 1 Brakes 50 150
    Truck 1 Maintenance 25 200
    Truck 1 Tires 100 100
    Truck 2 Brakes 0 200
    Truck 2 Maintenance 25 200
    Truck 2 Tires 0 200


    And so on...

  4. #4
    Board Regular James006's Avatar
    Join Date
    Apr 2009
    Posts
    3,587
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Sumproduct with Multiple Rows and Columns

    Have you tried to Insert a Pivot Table ...?

  5. #5
    New Member
    Join Date
    Oct 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sumproduct with Multiple Rows and Columns

    Good idea but I need to compare it to a budget file. The pivot table can be referenced but it's not very pretty and this will be seen by senior leadership. I'd like to use formulas, if I can.

  6. #6
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,361
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Sumproduct with Multiple Rows and Columns

    Welcome to the MrExcel board!

    See if this helps. Each formula copied down.

    Summary

    ABCDE
    1 Invoice 123Invoice 456Invoice 789
    2 AugustAugustSeptember
    3Truck 1Brakes50
    4Truck 2Tires 100
    5Truck 2Maintenance252525
    6Truck 3Brakes 50
    7Truck 4Maintenance 2525
    8
    9
    10 AugustYTD
    11Truck 1Brakes5050
    12Truck 1Maintenance00
    13Truck 1Tires00
    14Truck 2Brakes00
    15Truck 2Maintenance5075
    16Truck 2Tires100100

    Spreadsheet Formulas
    CellFormula
    C11=IFERROR(SUMIF(C$2:E$2,C$10,INDEX(C$3:E$7,MATCH(A11&"|"&B11,INDEX(A$3:A$7&"|"&B$3:B$7,0),0),0)),0)
    D11=IFERROR(SUM(INDEX(C$3:E$7,MATCH(A11&"|"&B11,INDEX(A$3:A$7&"|"&B$3:B$7,0),0),0)),0)


    Excel tables to the web >> Excel Jeanie HTML 4
    Last edited by Peter_SSs; Oct 23rd, 2019 at 11:32 PM.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  7. #7
    New Member
    Join Date
    Oct 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sumproduct with Multiple Rows and Columns

    That works. Thank you, Peter!

  8. #8
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,361
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Sumproduct with Multiple Rows and Columns

    Quote Originally Posted by jdavid1006 View Post
    That works. Thank you, Peter!
    You're welcome. Thanks for the follow-up.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

Some videos you may like

User Tag List

Tags for this Thread

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
  •