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?


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]WEEKLY
[/TD]
[TD]10/05[/TD]
[TD]10/12[/TD]
[TD]10/19[/TD]
[TD]10/26[/TD]
[TD]11/02[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Group1[/TD]
[TD]91[/TD]
[TD]12[/TD]
[TD]33[/TD]
[TD]64[/TD]
[TD]29[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Group2[/TD]
[TD]3[/TD]
[TD]6[/TD]
[TD]8[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Group3[/TD]
[TD]12[/TD]
[TD]69[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]56[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]MONTHLY
[/TD]
[TD]10/26[/TD]
[TD]11/23[/TD]
[TD]12/28[/TD]
[TD]1/25[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Group1[/TD]
[TD]?[/TD]
[TD]?[/TD]
[TD]?[/TD]
[TD]?[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Group2[/TD]
[TD]?[/TD]
[TD]?[/TD]
[TD]?[/TD]
[TD]?[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Group3[/TD]
[TD]?[/TD]
[TD]?[/TD]
[TD]?[/TD]
[TD]?[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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