Help with SumIfs again

[lilsaint01]

Good Day,

Please help me to create SUMIFS formula.
the data is on different sheets (Jan-Dec) and have multiple criteria.
i tried, but i cant figure it out.

Please help create formula in column D6 and D7 on formula sheet.
Criteria for D6 are : Months, Name, and Kg
Criteria for D7 are : Months, Name, and AMOUNT

the data for criteria can be found in sheet JAN-DEC.

1. แพ็คกล่อง --> Sheet Jan-Dec Column DI
2. ผลิตได้จริง kg. --> Sheet Jan-Dec Column Q
3. AMOUNT --> Sheet Jan-Dec Column DN
4. Month --> Sheet Jan-Dec Column B

Please check this link for the attachment file :

https://drive.google.com/file/d/12qYUVRLfcPEidMhr_gw-ndGvlPAjQGmf/view?usp=sharing

Thank you in advance ..

 

[Rijnsent]

Hi lilsaint,

For D6, you could start with:
=SUMIFS(JAN!Q2:Q500,JAN!A2:A500,30,JAN!B2:B500,1)
That basically calculates the SUMIFS. The next step is to make that formula more dynamic:
=SUMIFS(JAN!$Q$2:$Q$500,JAN!$A$2:$A$500,D4,JAN!$B$2:$B$500,$D$2)
That formula could be dragged to the right and it will calculate the values for the different days. The next step is to make the month dynamic, which is a bit harder as you have sheets named a bit illogical. If you would e.g. have names matching the short name of the months you could use the month number you type to derive the name like so:
=UPPER(TEXT(DATE(2019,D2,1),"mmm"))
If not, you'd need to type the name of the sheet yourself. Say you do that in D3, you could use the INDIRECT formula to bring it all together:
=SUMIFS(INDIRECT($D$3&"!$Q$2:$Q$500"),INDIRECT($D$3&"!$A$2:$A$500"),D4,INDIRECT($D$3&"!$B$2:$B$500"),$D$2)

Good luck!
Koen