SUMIF Help

drefiek1

New Member
Joined
May 7, 2018
Messages
5
Hi, I am trying to set up a formula which will sum the numbers in one column that meet the following criteria in two other columns: a particular month (in one adjacent column) and greater than or equal to +0.5 (in a second adjacent column). I've put the following formula in but can't get it to work. =SUMIFS(D2:D697,(SUMPRODUCT(--(MONTH($A$2:$A$697)=1),--($M$2:$M$697>=0.5))))


The range I want to sum based on the adjacent columns is D2:D697, with column A (date column) being January (=1) and also based on column M being greater than or equal to +0.5.






I'm also using the following which works elsewhere in the worksheet perfectly so I thought it would be a simple case of adding the SUMIF at the beginning.... =SUMPRODUCT(--(MONTH($A$2:$A$697)=1),--($M$2:$M$697>=0.5))


Thanks.
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

duggie33

Board Regular
Joined
Nov 19, 2018
Messages
77
Office Version
  1. 365
Platform
  1. Windows
Hi drefiek1,

Can you provide a small generic set data that can be tested and what you expect for an answer?

Thanks,

Doug
 

drefiek1

New Member
Joined
May 7, 2018
Messages
5
Hi Doug,

Sorry I am new here and haven't got the faintest idea how to attach data or anything but I've copied A38:E54 below, hope it works...


01-20150000.6
02-20150000.6
03-20154.59020672251.97106490.6
04-20153.36240616106.422569860.8
05-20154.81086566205.6641243151
06-20151.7361981749.2706227171.2
07-20153.4600896154.202966111.5
08-20150.7349337675.896122961.8
09-20154.802282550.292611652.1
10-20153.56508014113.648239212.4
11-201510.8861393303.44733602.5
12-20155.40441557224.030724272.6
01-20160002.5
02-20163.2508748827.432333612.2
03-20163.8185344205.742502221.7
04-20164.91242443303.517583821
05-20164.05624659216.6188831610.5

<tbody>
</tbody>



=SUMPRODUCT(--(MONTH($A$2:$A$73)=3),--($E$2:$E$73>=0.5))
This returns a count of 2 for all Marchs (column A) with +0.5 or above (column E). I've coloured the actual cells calculated in red



Now I want to use the same formula as above ^ but instead of returning a simple count I want it to SUM the values in column D based on the above criteria. So I need another new formula for this.

So for all March values >=0.5, it would return the number 31 (D40+D52). I've coloured these green

Furthermore, I also want to use the same criteria (March and +0.5 or above) but instead of counting (first example) or summing (second example) I want to average the length (column B) for the cells meeting the criteria. SO I need another new formula for this as well.

So for all March values >=0.5, it would return the number 4.20437056 (mean of B40 and B52). I've highlighted these in purple

I want the formulas to be fairly easy to change up where needed, so if I wanted a different month or a different >= value etc.

Thanks in advance and apologies if I've made an easy task of copying data here difficult!
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
Hi,

G3 formula for SUM of column D with your criteria.
H3 Array formula for AVERAGE of column B with your criteria, to be confirmed by CSE (Control, Shift, Enter), instructions below.
If you prefer Not to use an Array formula, use I3 normally entered alternative for AVERAGE:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">01-2015</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0.6</td><td style="text-align: right;;"></td><td style=";">SUM of D</td><td style=";">AVERAGE of B</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">02-2015</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0.6</td><td style="text-align: right;;"></td><td style="text-align: right;;">31</td><td style="text-align: right;;">4.20437056</td><td style="text-align: right;;">4.20437056</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">03-2015</td><td style="text-align: right;;">4.590207</td><td style="text-align: right;;">251.9711</td><td style="text-align: right;;">9</td><td style="text-align: right;;">0.6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">04-2015</td><td style="text-align: right;;">3.362406</td><td style="text-align: right;;">106.4226</td><td style="text-align: right;;">86</td><td style="text-align: right;;">0.8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">05-2015</td><td style="text-align: right;;">4.810866</td><td style="text-align: right;;">205.6641</td><td style="text-align: right;;">315</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">06-2015</td><td style="text-align: right;;">1.736198</td><td style="text-align: right;;">49.27062</td><td style="text-align: right;;">17</td><td style="text-align: right;;">1.2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">07-2015</td><td style="text-align: right;;">3.46009</td><td style="text-align: right;;">154.203</td><td style="text-align: right;;">11</td><td style="text-align: right;;">1.5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">08-2015</td><td style="text-align: right;;">0.734934</td><td style="text-align: right;;">75.89612</td><td style="text-align: right;;">6</td><td style="text-align: right;;">1.8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;">09-2015</td><td style="text-align: right;;">4.802283</td><td style="text-align: right;;">50.29261</td><td style="text-align: right;;">5</td><td style="text-align: right;;">2.1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;">10-2015</td><td style="text-align: right;;">3.56508</td><td style="text-align: right;;">113.6482</td><td style="text-align: right;;">21</td><td style="text-align: right;;">2.4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;">11-2015</td><td style="text-align: right;;">10.88614</td><td style="text-align: right;;">303.4473</td><td style="text-align: right;;">60</td><td style="text-align: right;;">2.5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;">12-2015</td><td style="text-align: right;;">5.404416</td><td style="text-align: right;;">224.0307</td><td style="text-align: right;;">27</td><td style="text-align: right;;">2.6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="text-align: right;;">01-2016</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">2.5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="text-align: right;;">02-2016</td><td style="text-align: right;;">3.250875</td><td style="text-align: right;;">27.43233</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2.2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style="text-align: right;;">03-2016</td><td style="text-align: right;;">3.818534</td><td style="text-align: right;;">205.7425</td><td style="text-align: right;;">22</td><td style="text-align: right;;">1.7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style="text-align: right;;">04-2016</td><td style="text-align: right;;">4.912424</td><td style="text-align: right;;">303.5176</td><td style="text-align: right;;">82</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style="text-align: right;;">05-2016</td><td style="text-align: right;;">4.056247</td><td style="text-align: right;;">216.6189</td><td style="text-align: right;;">161</td><td style="text-align: right;;">0.5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet627</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G3</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">(<font color="Red">MONTH(<font color="Green">A2:A73</font>)=3</font>)*(<font color="Red">E2:E73>=0.5</font>)*D2:D73</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I3</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">(<font color="Red">MONTH(<font color="Green">A2:A73</font>)=3</font>)*(<font color="Red">E2:E73>=0.5</font>)*B2:B73</font>)/SUMPRODUCT(<font color="Blue">(<font color="Red">MONTH(<font color="Green">$A$2:$A$73</font>)=3</font>)*(<font color="Red">$E$2:$E$73>=0.5</font>)</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">H3</th><td style="text-align:left">{=AVERAGE(<font color="Blue">IF(<font color="Red">MONTH(<font color="Green">A2:A73</font>)=3,IF(<font color="Green">E2:E73>=0.5,B2:B73</font>)</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

duggie33

Board Regular
Joined
Nov 19, 2018
Messages
77
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Here is what I came up with and it works on your data as best as I can tell.

I created another "table" to the right of your data. The first column (G) is for Months (1 through 12), the second column (H) is a count of the criteria you specified (Month=first column value and column E >=.5), third column (I) is the Sum of the values in B where the conditions are met, fourth column (J) is the mean of the column B values where the conditions are met. The fourth column is simply the third divided by the second. With all the equations working for one month, I copied them down for the other months. I put the .5 for the E>=.5 in cell L2 so it can be changed and the data all updates.

For the equations...
G2 = 1 (Month 1)
H2 =SUMPRODUCT(--(MONTH($A$1:$A$17)=$G2),--($E$1:$E$17>=$L$2))
I2 =SUMPRODUCT(--(MONTH($A$1:$A$17)=$G2),--($E$1:$E$17>=$L$2),$B$1:$B$17)
J2 = I2/H2 (you might want to add and IFERROR in case of #DIV/0! error)

You can easily add another column to sum the D values that meet your conditions. Copy and paste the values down to get the numbers for all months or set it up however works best for you.

Give it a shot on your test data and see how it works out.

Doug
 

drefiek1

New Member
Joined
May 7, 2018
Messages
5
Hi jtakw and Doug,

Both ideas work perfectly, I tried them both, thanks ever so much.

Cheers
 

Watch MrExcel Video

Forum statistics

Threads
1,109,543
Messages
5,529,456
Members
409,878
Latest member
DDhol
Top