SUMIF and INDEX/MATCH

P

Paoloskie

New member
Joined
Dec 15, 2019
Messages
13
Reaction score
0
Points
0
Excel Version(s)
Excel 2016
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.


Final Sheet.jpg


SA Sheet.jpg


Paolo
 

Attachments

  • Stock Sales By Period trial - WFA Excel Guru.xlsx
    315.8 KB · Views: 14
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.
 

Attachments

  • ExcelGuru10434Stock Sales By Period trial - WFA Excel Guru.xlsx
    314 KB · Views: 6
Last edited:
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
 
Actually, you can use the same formula without making changes to the Sales Analysis sheet.
 
Thank you sir/ maam! :wave:

Paolo
 
You must log in or register to reply here.
Back
Top