+ Reply to Thread
Results 1 to 9 of 9

Making SUMIF and OFFSET MATCH MATCH MATCH MATCH work

  1. #1
    Registered User
    Join Date
    07-28-2017
    Location
    England, UK
    MS-Off Ver
    Excel on Mac
    Posts
    4

    Making SUMIF and OFFSET MATCH MATCH MATCH MATCH work

    I've been trying to find a way to total up all figures in a given month between Jan 2016 to present, based on the unique payment identification. So far, I've tried everything from the simple VLOOKUP to the slightly more complex OFFSET MATCH's.

    I'm able to display the correct payment ID next to the date I am querying, but this only works for the first figure it finds in the year, and not the total for that particular payment ID. I'm looking for some guidance on where or how I need to structure my SUMIF formula so that I have the total amount paid according to the month / year and payment ID.

    I have one file with years 2016 (complete), 2017 (partly complete), 2018, 2019 and 2020, and the dates in the column are in a drop down. The idea is that according to the when the payment was made, I would choose from the drop down the relevant month. Then, based on the unique payment ID, I want it display the total that was paid on that unique payment ID.

    Any help would be appreciated.

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Making SUMIF and OFFSET MATCH MATCH MATCH MATCH work

    Sounds like sumifs should be able to do it.
    But we need some specifics. Can you attach a sample book?

    Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  3. #3
    Registered User
    Join Date
    07-28-2017
    Location
    England, UK
    MS-Off Ver
    Excel on Mac
    Posts
    4

    Re: Making SUMIF and OFFSET MATCH MATCH MATCH MATCH work

    Thanks JONMO1. I've attached a mock up workbook. Hope this is OK?
    Attached Files Attached Files

  4. #4
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Making SUMIF and OFFSET MATCH MATCH MATCH MATCH work

    Hi all- Paste this in I10 and copy down:
    =IF(ISBLANK($B10),"",SUMIFS($B$10:$B$26,$G$10:$G$26,$G10,$J$10:$J$26,">="&$A$1,$J$10:$J$26,"<="&$A$3))

    NOTE- Sample Rows 13 and 16 are identical. This formula sums BOTH. If you need to ignore duplicates, a different approach will be required.
    Last edited by leelnich; 07-29-2017 at 02:07 PM.

  5. #5
    Registered User
    Join Date
    07-28-2017
    Location
    England, UK
    MS-Off Ver
    Excel on Mac
    Posts
    4

    Re: Making SUMIF and OFFSET MATCH MATCH MATCH MATCH work

    Thanks LEELNICH. The formula you've provided works, but it sums up everything across all three months of Jan, Feb and Mar. What I needed was a sum for a particular month (Jan, Feb or Mar). So line 13 should be a total of $8 for Jan (as Mr. Trump only bought one spade in Jan). Whereas line 10 should be a total of $40 (as Mr. Trump bought 2 spades on 4th Mar, 1 spade on 11th Mar. However, he then bought a further 2 spades on 11th Mar. Therefore, 2+2+1 * $8 each = $40. Based on the unique payment ID of TRUMPFACE, I need a SUM for each item bought for a particular month, but to also add up any further purchases in that month.

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Making SUMIF and OFFSET MATCH MATCH MATCH MATCH work

    Try

    in I10 ...

    =IF(ISBLANK($B10),"",SUMIFS(INDEX('2016'!$A$9:$AE$100,,MATCH('Invoice Summary'!$J10,'2016'!$A$5:$AE$5,0)),'2016'!$A$9:$A$100,$G10&$H10))
    Attached Files Attached Files

  7. #7
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Making SUMIF and OFFSET MATCH MATCH MATCH MATCH work

    My mistake. Try this (in I10 and copy down):
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    I'm curious:
    1) on sheet '2016', why is the week of 1/29/2016 part of FEBRUARY? If it belongs, then why does 2/26/2016 ALSO belong?
    2) cell K10, the RED section (expression-expression) always = 0. What is your intent?
    =IF(ISBLANK($B10),"",IF(AND($J10>=$A$1,$J10<=$A$3),OFFSET('2016'!$A$4,MATCH($M10,'2016'!$A:$A,0)-4,MATCH('Invoice Summary'!$J10,'2016'!$J$5:$AE$5,0)+9,1,MATCH("Amount Paid",'2016'!$J$8:$AE$8,0)-MATCH("Amount Paid",'2016'!$J$8:$AE$8,0)+1),""))

    Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee
    Last edited by leelnich; 07-30-2017 at 09:44 AM.

  8. #8
    Registered User
    Join Date
    07-28-2017
    Location
    England, UK
    MS-Off Ver
    Excel on Mac
    Posts
    4

    Re: Making SUMIF and OFFSET MATCH MATCH MATCH MATCH work

    @JOHNTOPLEY - Your suggestion worked a treat. I had to adjust the formula slightly (i.e $G10&$H10 was replaced with $m10 as this was a CONCATENATE of your two columns).

    @LEELNICH - John was able to solve this issue with his formula suggestion so haven't tried yours. RE: the Feb dates, this is intentional as my work financial calendar is different to the normal Georgian calendar, and therefore, certain weeks won't always fall into the normal calendar. Some other months have an offset of week ends too.

    RE: the two expressions for the end of my OFFSET, I saw that I would have needed this to balance my offset formula.

    I'm eager to know why in John's formula, I have no MATCH for the ROWS, but only the COLUMNS? Also, as my MATCH formula wasn't aligning to the correct column, I added a "-1" so that the formula would return the correct column data. The point being that as I haven't used the OFFSET in John's formula, I still needed the formula to go to a certain amount of columns back, so this can be achieved by adding (or subtracting) the amount of columns until you hit the desired data column.

    Therefore, hats off to you both; JOHNTOPLEY and LEELNICH.

  9. #9
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Making SUMIF and OFFSET MATCH MATCH MATCH MATCH work

    Happy to help, thank you for the rep!
    I'm eager to know why in John's formula, I have no MATCH for the ROWS, but only the COLUMNS?
    If you leave the row parameter of the INDEX function blank (John) or set to 0 (Lee), it returns all rows in the index range, and thereby the ENTIRE designated column of that index range. Both formulae used this method to manifest a "sum range", then used different methods to decide which rows within it to sum.
    Last edited by leelnich; 07-30-2017 at 11:15 AM.

+ 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] Quick INDEX MATCH MATCH OFFSET question
    By franb123 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-25-2017, 02:56 AM
  2. INDEX MATCH MATCH/OFFSET MATCH MATCH with named ranges
    By Andrew-Mark in forum Excel General
    Replies: 3
    Last Post: 02-27-2015, 10:56 PM
  3. Replies: 1
    Last Post: 10-06-2014, 02:13 PM
  4. [SOLVED] Dynamic Name Range using Offset&Match with Match based off a different column
    By mdlpjr in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-16-2013, 06:33 PM
  5. Replies: 3
    Last Post: 05-08-2013, 02:10 PM
  6. [SOLVED] index match with row information offset from the match cell
    By smls in forum Excel General
    Replies: 7
    Last Post: 08-30-2012, 09:48 AM
  7. Replies: 2
    Last Post: 03-16-2012, 12:03 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