+ Reply to Thread
Results 1 to 19 of 19

Monthly report using index match

  1. #1
    Forum Contributor
    Join Date
    07-06-2020
    Location
    zimbabwe
    MS-Off Ver
    Excel 365
    Posts
    457

    Monthly report using index match

    Hi

    I'm kindly asking for a Less complicated formula which show the Report by Month so that when I click on cell D5, I select the month and the report for that month comes out. The current formulas produces report for all months. See my desired outcome on the attached excel sheet.

    Thank you.

    Regards,

    Sunboy
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Monthly report using index match

    One thing is sure. It will NOT be less complicated!!

    What if Person X has two different invoices in one month? Two lines or one?
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,410

    Re: Monthly report using index match

    I was just thinking the same - the formula in the attachment is in fact very basic!
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  4. #4
    Forum Contributor
    Join Date
    07-06-2020
    Location
    zimbabwe
    MS-Off Ver
    Excel 365
    Posts
    457

    Re: Monthly report using index match

    Hi

    If the customer has two or more different invoices in one month, they should be recorded separately not combined. I am happy with the current formula and is not complicated but it does not produce reports as per month when I click on cell D5.

    Thank you

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Monthly report using index match

    The key change is the formula to return UNIQUE invoice numbers for ECH month. As I said, it is NOT less complicated. It IS what is needed.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    I may have changed other formulae in the sheet, as I deleted all formuae and started from scratch.
    Attached Files Attached Files

  6. #6
    Forum Contributor
    Join Date
    07-06-2020
    Location
    zimbabwe
    MS-Off Ver
    Excel 365
    Posts
    457

    Re: Monthly report using index match

    Hi

    I have tried your formula but the Invoice formula is not working properly. When I drag the formula to the below rows it repeats the same first invoice to all below rows.

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Monthly report using index match

    You have done something wrong, I think.

    1. Did it work as desied in my sheet?

    2. In which cell in your real sheet does the formula start?

    3. In that cell, copy paste the formula here.

  8. #8
    Forum Contributor
    Join Date
    07-06-2020
    Location
    zimbabwe
    MS-Off Ver
    Excel 365
    Posts
    457

    Re: Monthly report using index match

    Hi

    IFERROR(INDEX('ZW$ Sales'!G:G,AGGREGATE(15,6,ROW('ZW$ Sales'!$G$6:$G$10000)/(('ZW$ Sales'!$B$6:$B$10000>=$D$1)*('ZW$ Sales'!$B$6:$B$10000<=EOMONTH($D$1,0))*(COUNTIF($F10:F10,'ZW$ Sales'!$G$6:$G$10000)=0)),1)),"")

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Monthly report using index match

    Which cell is this in (my second Q)?

  10. #10
    Forum Contributor
    Join Date
    07-06-2020
    Location
    zimbabwe
    MS-Off Ver
    Excel 365
    Posts
    457

    Re: Monthly report using index match

    The formula starts in cell F11

  11. #11
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Monthly report using index match

    Mmmm. Should be OK!! Are calculation options set to manual, by any chance?

  12. #12
    Forum Contributor
    Join Date
    07-06-2020
    Location
    zimbabwe
    MS-Off Ver
    Excel 365
    Posts
    457

    Re: Monthly report using index match

    I don't know why it is it repeating the same first invoice to all below rows, when I drag the formula.

  13. #13
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Monthly report using index match

    To repeat myself (again):

    Are calculation options set to manual?

    Did it work as expected on my sheet?

  14. #14
    Forum Contributor
    Join Date
    07-06-2020
    Location
    zimbabwe
    MS-Off Ver
    Excel 365
    Posts
    457

    Re: Monthly report using index match

    I don't understand what you mean by: Are calculation options set to manual?
    Yes it worked as I wanted but when I adjusted certain invoices on the General sales, the formula just did not work, it did not produce any figures it was just blank sheet.

  15. #15
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Monthly report using index match

    I need to see the sheet.

  16. #16
    Forum Contributor
    Join Date
    07-06-2020
    Location
    zimbabwe
    MS-Off Ver
    Excel 365
    Posts
    457

    Re: Monthly report using index match

    Hi

    Find the attached sheet
    Attached Files Attached Files

  17. #17
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Monthly report using index match

    Apologies. It was a mistake that you made... which i did not spot back at Post 8

    =IFERROR(INDEX('ZW$ Sales'!G:G,AGGREGATE(15,6,ROW('ZW$ Sales'!$G$6:$G$10000)/(('ZW$ Sales'!$B$6:$B$10000>=$D$6)*('ZW$ Sales'!$B$6:$B$10000<=EOMONTH($D$6,0))*(COUNTIF(F$13:F13,'ZW$ Sales'!$G$6:$G$10000)=0)),1)),"")

    You had put the $ in the wrong place. In a rush, I made sure it was THERE, but did not check it was in the right place. So your fault... and mine!!
    Attached Files Attached Files

  18. #18
    Forum Contributor
    Join Date
    07-06-2020
    Location
    zimbabwe
    MS-Off Ver
    Excel 365
    Posts
    457

    Re: Monthly report using index match

    Hi Glenn Kennedy

    Thank you for helping me.

    Regards,

    Sunboy

  19. #19
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Monthly report using index match

    You're welcome. Apols for the 10-post delay in getting you sorted out!! Thanks for the rep. Can you also mark the post as solved?




    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

+ 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. Correct combination of SUMIF/INDEX/MATCH to summarise monthly totals
    By QuantumP in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-13-2017, 07:10 AM
  2. Replies: 2
    Last Post: 02-11-2016, 06:31 AM
  3. Monthly, Bi-monthly, Quarterly, Yearly Report Tracking Help
    By eugene_lys in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-26-2014, 11:08 PM
  4. Replies: 1
    Last Post: 08-15-2013, 02:15 PM
  5. How to collect daily report from spread sheet and accumulate for monthly report
    By yshguru in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-22-2013, 05:17 PM
  6. Replies: 2
    Last Post: 01-14-2013, 08:19 AM
  7. Report Table: VLookUp, Match, Index Formula
    By NSTAR in forum Excel General
    Replies: 12
    Last Post: 03-16-2010, 10:44 AM

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