Stumped - SUMIFS for Horizontal and Vertical Criteria

[GKMAC]

I'm trying to get a sum by Month (from dates in row 1) and by group (in column A) using sumifs. but am stumped. suggestions?

	A	B	C	D	E	F
1	WEEKLY	10/05	10/12	10/19	10/26	11/02
2	Group1	91	12	33	64	29
3	Group2	3	6	8	1	0
4	Group3	12	69	3	4	56
5
6
7	MONTHLY	10/26	11/23	12/28	1/25
8	Group1	?	?	?	?
9	Group2	?	?	?	?
10	Group3	?	?	?	?
11

<tbody>
</tbody>

My formula for B8 is

=SUMIFS(B2:F4,$A2:A4,B8,B1:F1,">="&A7-26,B1:F1,"<="&A7)

 

[kweaver]

What should B8 be? Sum of all the 10/xx values for Group 1?

If it should be 200, try this in B8 and fill across and down:

=SUMPRODUCT((MONTH(B$7)=MONTH($B$1:$F$1))*($B2:$F2))

 

[DanteAmor]

Try this


<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:95.05px;" /><col style="width:95.05px;" /><col style="width:95.05px;" /><col style="width:95.05px;" /><col style="width:95.05px;" /><col style="width:95.05px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; ">WEEKLY</td><td style="background-color:#ffff00; text-align:right; ">05/oct/2019</td><td style="background-color:#ffff00; text-align:right; ">12/oct/2019</td><td style="background-color:#ffff00; text-align:right; ">19/oct/2019</td><td style="background-color:#ffff00; text-align:right; ">26/oct/2019</td><td style="background-color:#ffff00; text-align:right; ">02/nov/2019</td><td style="background-color:#ffff00; text-align:right; ">09/nov/2019</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >Group1</td><td style="text-align:right; ">91</td><td style="text-align:right; ">12</td><td style="text-align:right; ">33</td><td style="text-align:right; ">64</td><td style="text-align:right; ">29</td><td style="text-align:right; ">34</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Group2</td><td style="text-align:right; ">3</td><td style="text-align:right; ">6</td><td style="text-align:right; ">8</td><td style="text-align:right; ">1</td><td style="text-align:right; ">15</td><td style="text-align:right; ">35</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >Group3</td><td style="text-align:right; ">12</td><td style="text-align:right; ">69</td><td style="text-align:right; ">3</td><td style="text-align:right; ">4</td><td style="text-align:right; ">56</td><td style="text-align:right; ">36</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="background-color:#bfbfbf; "> </td><td style="background-color:#bfbfbf; "> </td><td style="background-color:#bfbfbf; "> </td><td style="background-color:#bfbfbf; "> </td><td style="background-color:#bfbfbf; "> </td><td style="background-color:#bfbfbf; "> </td><td style="background-color:#bfbfbf; "> </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="background-color:#92d050; ">MONTHLY</td><td style="background-color:#92d050; text-align:right; ">01/oct/2019</td><td style="background-color:#92d050; text-align:right; ">01/nov/2023</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >Group1</td><td style="text-align:right; ">200</td><td style="text-align:right; ">63</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td >Group2</td><td style="text-align:right; ">18</td><td style="text-align:right; ">50</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td >Group3</td><td style="text-align:right; ">88</td><td style="text-align:right; ">92</td><td > </td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B8</td><td >=SUMPRODUCT((MONTH($B$1:$G$1)=MONTH(B$7))*($A$2:$A$4=$A8)*($B$2:$G$4))</td></tr></table></td></tr></table>

Copy to the right and then down

 

[GKMAC]

I may have over simplified the example. Each row 2-4 is a sum of collapsed input. I've put the example into a file (link below). Each approach is captured in the example with the results.

https://www.dropbox.com/s/k0t4fvw1j0rbk3k/Book2.xlsx?dl=1