Sumifs+index formula help!!

[WormJacob]

Hello ,

NEw to this forum and would to get help(first thank you in advanced)!!

I need help to create a formula that sum the following Summary below?

<colgroup><col><col><col><col><col><col><col><col><col><col span="2"><col><col></colgroup><tbody>
</tbody>

 

[kvsrinivasamurthy]

Second Table starts From B14.
Months NAME in C15,E15,G15 is to be replaced by 1/1/18,1/2/18,1/3/18 (dd/mm/yy)
B16=Fruits

Formula in C17, then drag across

=SUMPRODUCT(($F$2:$M$7)*((($A$2:$A$7=$B17)*($D$2:$D$7=C$16))*(MONTH($F$1:$M$1)=MONTH(INDEX($C$15:$H$15,IF(ISODD(COLUMNS($C$15:C$15)),COLUMNS($C$15:C$15),COLUMNS($C$15:C$15)-1))))))

 

[Eric W]

Similar in concept:

	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Part	Part Description	Lead-time	Order Type	50 wk Total	12/25/2017	1/1/2018	1/8/2018	1/29/2018	2/5/2018	2/12/2018	2/26/2018	3/5/2018
2	Apple	Whole	3	PO	20	20	0	0	0	0	0	0	0
3	Apple	Whole	3	Planned	740	0	0	20	20	20	40	0	20
4	Banana	Whole	5	PO	20	20	0	0	0	0	0	0	0
5	Pears	Whole	5	Planned	720	0	0	0	0	20	40	20	0
6	Pears	Whole	15	Planned	740	0	0	0	20	20	40	0	20
7	Orange	Whole	15	PO	26	6	0	20	0	0	0	0	0
8
9
10
11		2018
12		Jan		Feb		Mar
13	Fruits	Planned	PO	Planned	PO	Planned	PO
14	Apple	40	0	60	0	20	0
15	Banana	0	0	0	0	0	0
16	Pears	20	0	140	0	20	0
17	Orange	0	20	0	0	0	0

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula B14 =SUMPRODUCT(($F$2:$M$7)*($D$2:$D$7=B$13)*(YEAR($F$1:$M$1)=$B$11)*(TEXT($F$1:$M$1,"mmm")=INDEX($B$12:$G$12,COLUMNS($B2:B2)-MOD(COLUMN(),2)))*($A$2:$A$7=$A14)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

The layout of your tables made it a bit tricky. I assume you'd put the result table on another sheet. The year and months are probably merged cells. Put the formula in B14, drag down and across as needed.

 

[WormJacob]

Thanks ERIC!! Really appreciated.!!!!! Quite honest that table are sooo tricky and this is how i received from my customer and im trying to Simplified it instead of manual modifying it each time.. Again.. Thanks for the support

 

[Eric W]

Glad to help!

 

[WormJacob]

Hi Eric/Anyone?

Formula work on same sheet.. When i created Summary on NEW Sheet(refer sheet and ranges )

Here is new Formula: It came out #n/A and 0 on the rest of the column?

This is the Summary(Summary)

Forecast0109(data)

 

[Eric W]

Try:

	A	B	C	D	E	F	G	H
1			2018
2			Jan		Feb		Mar
3	Part		Planned	PO	Planned	PO	Planned	PO
4	Apple		40	0	60	0	20	0
5	Banana		0	0	0	0	0	0
6	Pears		20	0	140	0	20	0
7	Orange		0	20	0	0	0	0

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Summary​

Worksheet Formulas Cell Formula C4 =SUMPRODUCT((Forecast!$I$2:$P$7)*(Forecast!$G$2:$G$7=C$3)*(YEAR(Forecast!$I$1:$P$1)=$C$1)*(TEXT(Forecast!$I$1:$P$1,"mmm")=INDEX($C$2:$H$2,FLOOR(COLUMNS($C2:C2)-1,2)+1))*(Forecast!$A$2:$A$7=$A4)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

I changed the formula to use FLOOR instead of MOD, which should eliminate one type of problem when you insert columns. Other things to look for is I have my Months in C2,E2,G2, and you have them in D2,F2, and H2. Also, F2 is a date, not a month abbreviation. If you want to Merge and Center C2:D2 (and the rest of the months), then it looks better and the formula works. Also, I can adapt it to use either the 3-letter abbreviation, or the first of the month as a date if you want.

Also, consider using a tool like the HTML Maker in my signature. It makes it much easier for someone to copy your data to work with. Your screen prints are good, but many people won't spend the time to type in a large table. That's why I used the old data, but I moved it to your new ranges.

 

[WormJacob]

Thank Eric.. I will try this.. I will them on HTMl maker if any thing wrongs comes up (on my side)! Thank you

 

[WormJacob]

Hi Eric/Someone?

Sorry i couldn't load on HTML. 2 sheet (1tab- Summary) & (2nd FRC-Jan18 data) .. Not error but just not calculating ... All are ZERO


Summary

	A	C	D	E	F	G	H
1		2018
2	Months	Jan	Feb	Mar
3	Part	Planned	PO	Planned	PO	Planned	PO
4	1647670	0	0	0	0	0	0
5	2104179	0	0	0	0	0	0
6	3118228	0	0	0	0	0	0
7	3118237	0	0	0	0	0	0

<colgroup><col><col><col span="6"></colgroup><tbody>
</tbody>

Cell	Formula
C4	=SUMPRODUCT(('FRC-Jan18'!$E$2:$BB$103)*('FRC-Jan18'!$C$2:$C$103=AA$3)*(YEAR('FRC-Jan18'!$E$1:$BB$1)=$C$1)*(TEXT('FRC-Jan18'!$E$1:$BB$1,"mmm")=INDEX($C$2:$Y$2,FLOOR(COLUMNS($C2:AA2)*1,2)+1))*('FRC-Jan18'!$A$2:$A$103=$A4))

<colgroup><col><col></colgroup><tbody>
</tbody>

FRC-Jan18

	A	B	C	D	E	F	G	H	I	J
1	Part	Part Description	Order Type	50 wk Total	1/8/2018	1/15/2018	1/22/2018	1/29/2018	2/5/2018	2/12/2018
2	3951894	Whole	Planned	294	0	42	0	84	42	0
3	3951894	Whole	Planned	504	0	0	0	0	0	0
4	4851751	Whole	No Orders	0	0	0	0	0	0	0
5	3305442	Whole	No Orders	0	0	0	0	0	0	0
6	3305524	Whole	Planned	18	0	0	0	3	0	0
7	4109268	Whole	Planned	8	0	0	0	0	0	0
8	4142630	Whole	Planned	12	0	0	0	0	0	0
9	4142630	Whole	PO	0	0	0	0	0	0	0
10	4157695	Whole	PO	1	0	1	0	0	0	0
11	4157695	Whole	Planned	10	0	0	0	0	0	0

<colgroup><col><col span="3"><col><col><col span="3"><col><col></colgroup><tbody>
</tbody>

Thank you again in advance!
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style><style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style><style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style><style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

 

[Eric W]

You missed changing one range reference, and had another typo. Try:

C4:

=SUMPRODUCT(('FRC-Jan18'!$E$2:$BB$103)*('FRC-Jan18'!$C$2:$C$103=C$3)*(YEAR('FRC-Jan18'!$E$1:$BB$1)=$C$1)*(TEXT('FRC-Jan18'!$E$1:$BB$1,"mmm")=INDEX($C$2:$Y$2,FLOOR(COLUMNS($C2:C2)-1,2)+1))*('FRC-Jan18'!$A$2:$A$103=$A4))