Add certain rows based on column header

[jfrad]

So I may be over thinking this, but I need a formula that will take any column with the header MONDAY and sum rows 4-6.


[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD="align: center"]MONDAY[/TD]
[TD="align: center"]MONDAY[/TD]
[TD="align: center"]TUESDAY[/TD]
[TD="align: center"]TUESDAY[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD] [/TD]
[TD="align: center"]AM[/TD]
[TD="align: center"]PM[/TD]
[TD="align: center"]AM[/TD]
[TD="align: center"]PM[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD="align: center"]A[/TD]
[TD]50[/TD]
[TD]250[/TD]
[TD]200[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD="align: center"]B[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]250[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD="align: center"]C[/TD]
[TD]50[/TD]
[TD]100[/TD]
[TD]50[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD="align: center"]D[/TD]
[TD]200[/TD]
[TD]200[/TD]
[TD]50[/TD]
[TD]250[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD="align: center"]E[/TD]
[TD]200[/TD]
[TD]50[/TD]
[TD]100[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD="align: center"]F[/TD]
[TD]250[/TD]
[TD]250[/TD]
[TD]200[/TD]
[TD]50[/TD]
[/TR]
</tbody>[/TABLE]

 

[VBA Geek]

Excel 2013/2016
	A	B	C	D	E	F
1	TUESDAY	MONDAY	MONDAY	TUESDAY
2	AM	PM	AM	PM		600
3	50	250	200	100
4	100	100	250	50
5	50	100	50	200
6	200	200	50	250
7	200	50	100	50
8	250	250	200	50
Sheet2

Cell Formulas
Range	Formula
F2	{=SUMPRODUCT(MMULT(--(A1:D1="MONDAY"),TRANSPOSE(A3:D8)),{0,1,0,1,0,0})}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[jfrad]

So I tried this in my workbook and I'm getting a #VALUE ! error.

Here is what the formula looks like with my workbook data =SUMPRODUCT(MMULT(--(Sheet1!A1:$P$1=Sheet2!O7),TRANSPOSE(Sheet1!$A$71:$P$78)),{0,1,0,1,0,0})

I've never used the MMULT and TRANSPOSE functions before so I'm not sure if I did it correctly.

 

[VBA Geek]

You need to adjust the last part of the formula to match the rows that you want to sum. So you are looking at rows 71 to 78, if you do:

=SUMPRODUCT(MMULT(--(Sheet1!A1:$P$1=Sheet2!O7),TRANSPOSE(Sheet1!$A$71:$P$78)),{0,1,0,1,0,0,0,0})

you will sum only rows 72 and 74