Results 1 to 6 of 6

Thread: SUMIF and INDEX/MATCH

  1. #1
    Seeker Paoloskie's Avatar
    Join Date
    Dec 2019
    Posts
    6
    Articles
    0
    Excel Version
    Excel 2016

    SUMIF and INDEX/MATCH



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi there ExcelGuru community,

    A properous new year to you all. Anyways, I want to sum all the year 2017, 2018 and 2019 with their respective CODES in the other sheet (Sales Analysis). I am testing for hours already but still no progress. I know it seems easy but would very be pleased if somebody can assist. I attached the document as well. Thank you so much. Bless you.


    Click image for larger version. 

Name:	Final Sheet.jpg 
Views:	153 
Size:	86.2 KB 
ID:	9561


    Click image for larger version. 

Name:	SA Sheet.jpg 
Views:	132 
Size:	103.1 KB 
ID:	9562


    Paolo
    Attached Files Attached Files

  2. #2
    Acolyte bobjglover@gmail.com's Avatar
    Join Date
    Aug 2015
    Posts
    57
    Articles
    0
    Excel Version
    1901
    Can you be a little more specific on what your looking for?

  3. #3
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,693
    Articles
    0
    Excel Version
    365
    In cell F6 of the Final sheet, this formula:
    Code:
    =SUMPRODUCT(INDEX('Sales Analysis'!$E$7:$AN$547,MATCH($D6,'Sales Analysis'!$A$7:$A$547,0),0)*(YEAR('Sales Analysis'!$E$6:$AN$6)=F$5))
    Copy across 3 cells and down as far as you need.
    But first you must change the text values in cells E6:AN6 to real dates:
    First change the format of those cells to General (they're formatted Text at the moment),
    then select each one, start editing it, but change nothing and press Enter. It should become a date, the first day of the respective month.
    Check the results on the Final sheet are correct.
    Last edited by p45cal; 2020-01-18 at 05:30 PM.

  4. #4
    Seeker Paoloskie's Avatar
    Join Date
    Dec 2019
    Posts
    6
    Articles
    0
    Excel Version
    Excel 2016
    Hi P45cal,

    Actually I generated the 'Sales Analysis' sheet using our ERP system. As much as possible, I need to adapt to the format of the Sales Analysis. However, thank you for your help.

    Paolo

  5. #5
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,693
    Articles
    0
    Excel Version
    365
    Actually, you can use the same formula without making changes to the Sales Analysis sheet.

  6. #6
    Seeker Paoloskie's Avatar
    Join Date
    Dec 2019
    Posts
    6
    Articles
    0
    Excel Version
    Excel 2016
    Thank you sir/ maam!

    Paolo

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •