+ Reply to Thread
Results 1 to 7 of 7

Cumulative SumIF or Index match across multiple columns

  1. #1
    Registered User
    Join Date
    03-29-2020
    Location
    New Zealand
    MS-Off Ver
    Excel 2016
    Posts
    3

    Cumulative SumIF or Index match across multiple columns

    Hi all, first time poster, long time googler here.

    My issue is I am looking for a cumulative sumif or index match type solution to solve my problem (example attached). I have data over 2 sheets, first sheet is where I want the cumulative totals to be listed by month (in columns) and the second sheet is where the data is stored and it can be in any order in the rows on the sheet and will be added to over time, that's why I don't want to use a fixed =SUM formula as the order of the rows could change.

    If I need to add more info to help solve this pls let me know
    Attached Files Attached Files

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,257

    Re: Cumulative SumIF or Index match across multiple columns

    Hi Andy and welcome to the forum,

    I've seen better sample data files . I couldn't quite understand what you wanted , so I made my own data in the attached . Then I did a Pivot Table which I think you are looking for. Learn more about Pivot Tables and Dynamic Named Ranges and I think it will do what you want.
    P{ivot Running Total by Column Date.xlsx
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    03-29-2020
    Location
    New Zealand
    MS-Off Ver
    Excel 2016
    Posts
    3

    Re: Cumulative SumIF or Index match across multiple columns

    Thanks Marvin P. I know how to do a pivot table so not exactly what I was looking for, it will work however if there is no other solution. I have added some more notes to my example and re uploaded it.
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,654

    Re: Cumulative SumIF or Index match across multiple columns

    This proposal inserts a column (C) into the table on the summary sheet.
    The formula for cell D6 is: =SUM(INDEX(Data!C:C,MATCH($B6,Data!$B:$B,0),1),C6)
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,654

    Re: Cumulative SumIF or Index match across multiple columns

    Here is a formula you could paste into cell C6 (file attached to post #3) that does not require inserting another column: =SUMPRODUCT((Data!$B$6:$B$21=$B6)*(Data!$C$6:C$21))
    After pasting into cell C6 drag the fill handle down and across.
    Let us know if you have any questions.

  6. #6
    Registered User
    Join Date
    03-29-2020
    Location
    New Zealand
    MS-Off Ver
    Excel 2016
    Posts
    3

    Re: Cumulative SumIF or Index match across multiple columns

    Hi there JeteMc. The sum product is perfect thanks!

  7. #7
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,654

    Re: Cumulative SumIF or Index match across multiple columns

    You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

+ 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. Multiple references in one formula. Tried SUMIF, VLOOKUP, INDEX, and MATCH.
    By lazers26 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-24-2019, 10:23 AM
  2. [SOLVED] sumif, index, match + SUM MULTIPLE columns
    By dluhut in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-08-2016, 05:16 PM
  3. VBA code for combined sumif index/match for multiple criteria
    By megaheinz in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 02-16-2016, 11:44 AM
  4. Sumif Index match with multiple column same criteria
    By lilym9053 in forum Excel General
    Replies: 3
    Last Post: 01-08-2016, 12:38 PM
  5. [SOLVED] index match and sumproduct or sumif on multiple criteria
    By tabkaz in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 07-02-2014, 06:20 AM
  6. [SOLVED] need help on Sumif with index and match multiple criteria
    By muheebrahman in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 05-11-2013, 12:53 AM
  7. Replies: 2
    Last Post: 01-27-2012, 01:32 PM

Tags for this Thread

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