SUMIF Help

[drefiek1]

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.

 

[duggie33]

Hi drefiek1,

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

Thanks,

Doug

 

[drefiek1]

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...


[TABLE="width: 435"]
<tbody>[TR]
[TD="class: xl67, width: 87, align: right"]01-2015[/TD]
[TD="class: xl66, width: 87, align: right"]0[/TD]
[TD="class: xl66, width: 87, align: right"]0[/TD]
[TD="class: xl66, width: 87, align: right"]0[/TD]
[TD="class: xl66, width: 87, align: right"]0.6[/TD]
[/TR]
[TR]
[TD="class: xl67, align: right"]02-2015[/TD]
[TD="class: xl66, align: right"]0[/TD]
[TD="class: xl66, align: right"]0[/TD]
[TD="class: xl66, align: right"]0[/TD]
[TD="class: xl66, align: right"]0.6[/TD]
[/TR]
[TR]
[TD="class: xl68, align: right"]03-2015[/TD]
[TD="class: xl69, align: right"]4.59020672[/TD]
[TD="class: xl66, align: right"]251.971064[/TD]
[TD="class: xl70, align: right"]9[/TD]
[TD="class: xl71, align: right"]0.6[/TD]
[/TR]
[TR]
[TD="class: xl67, align: right"]04-2015[/TD]
[TD="class: xl66, align: right"]3.36240616[/TD]
[TD="class: xl66, align: right"]106.422569[/TD]
[TD="class: xl66, align: right"]86[/TD]
[TD="class: xl66, align: right"]0.8[/TD]
[/TR]
[TR]
[TD="class: xl67, align: right"]05-2015[/TD]
[TD="class: xl66, align: right"]4.81086566[/TD]
[TD="class: xl66, align: right"]205.664124[/TD]
[TD="class: xl66, align: right"]315[/TD]
[TD="class: xl66, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl67, align: right"]06-2015[/TD]
[TD="class: xl66, align: right"]1.73619817[/TD]
[TD="class: xl66, align: right"]49.2706227[/TD]
[TD="class: xl66, align: right"]17[/TD]
[TD="class: xl66, align: right"]1.2[/TD]
[/TR]
[TR]
[TD="class: xl67, align: right"]07-2015[/TD]
[TD="class: xl66, align: right"]3.4600896[/TD]
[TD="class: xl66, align: right"]154.202966[/TD]
[TD="class: xl66, align: right"]11[/TD]
[TD="class: xl66, align: right"]1.5[/TD]
[/TR]
[TR]
[TD="class: xl67, align: right"]08-2015[/TD]
[TD="class: xl66, align: right"]0.73493376[/TD]
[TD="class: xl66, align: right"]75.8961229[/TD]
[TD="class: xl66, align: right"]6[/TD]
[TD="class: xl66, align: right"]1.8[/TD]
[/TR]
[TR]
[TD="class: xl67, align: right"]09-2015[/TD]
[TD="class: xl66, align: right"]4.8022825[/TD]
[TD="class: xl66, align: right"]50.2926116[/TD]
[TD="class: xl66, align: right"]5[/TD]
[TD="class: xl66, align: right"]2.1[/TD]
[/TR]
[TR]
[TD="class: xl67, align: right"]10-2015[/TD]
[TD="class: xl66, align: right"]3.56508014[/TD]
[TD="class: xl66, align: right"]113.648239[/TD]
[TD="class: xl66, align: right"]21[/TD]
[TD="class: xl66, align: right"]2.4[/TD]
[/TR]
[TR]
[TD="class: xl67, align: right"]11-2015[/TD]
[TD="class: xl66, align: right"]10.8861393[/TD]
[TD="class: xl66, align: right"]303.44733[/TD]
[TD="class: xl66, align: right"]60[/TD]
[TD="class: xl66, align: right"]2.5[/TD]
[/TR]
[TR]
[TD="class: xl67, align: right"]12-2015[/TD]
[TD="class: xl66, align: right"]5.40441557[/TD]
[TD="class: xl66, align: right"]224.030724[/TD]
[TD="class: xl66, align: right"]27[/TD]
[TD="class: xl66, align: right"]2.6[/TD]
[/TR]
[TR]
[TD="class: xl67, align: right"]01-2016[/TD]
[TD="class: xl66, align: right"]0[/TD]
[TD="class: xl66, align: right"]0[/TD]
[TD="class: xl66, align: right"]0[/TD]
[TD="class: xl66, align: right"]2.5[/TD]
[/TR]
[TR]
[TD="class: xl67, align: right"]02-2016[/TD]
[TD="class: xl66, align: right"]3.25087488[/TD]
[TD="class: xl66, align: right"]27.4323336[/TD]
[TD="class: xl66, align: right"]1[/TD]
[TD="class: xl66, align: right"]2.2[/TD]
[/TR]
[TR]
[TD="class: xl68, align: right"]03-2016[/TD]
[TD="class: xl69, align: right"]3.8185344[/TD]
[TD="class: xl66, align: right"]205.742502[/TD]
[TD="class: xl70, align: right"]22[/TD]
[TD="class: xl71, align: right"]1.7[/TD]
[/TR]
[TR]
[TD="class: xl67, align: right"]04-2016[/TD]
[TD="class: xl66, align: right"]4.91242443[/TD]
[TD="class: xl66, align: right"]303.517583[/TD]
[TD="class: xl66, align: right"]82[/TD]
[TD="class: xl66, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl67, align: right"]05-2016[/TD]
[TD="class: xl66, align: right"]4.05624659[/TD]
[TD="class: xl66, align: right"]216.618883[/TD]
[TD="class: xl66, align: right"]161[/TD]
[TD="class: xl66, align: right"]0.5[/TD]
[/TR]
</tbody>[/TABLE]



=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]

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:

Book1
	A	B	C	D	E	F	G	H	I
2	01-2015	0	0	0	0.6		SUM of D	AVERAGE of B
3	02-2015	0	0	0	0.6		31	4.20437056	4.20437056
4	03-2015	4.590207	251.9711	9	0.6
5	04-2015	3.362406	106.4226	86	0.8
6	05-2015	4.810866	205.6641	315	1
7	06-2015	1.736198	49.27062	17	1.2
8	07-2015	3.46009	154.203	11	1.5
9	08-2015	0.734934	75.89612	6	1.8
10	09-2015	4.802283	50.29261	5	2.1
11	10-2015	3.56508	113.6482	21	2.4
12	11-2015	10.88614	303.4473	60	2.5
13	12-2015	5.404416	224.0307	27	2.6
14	01-2016	0	0	0	2.5
15	02-2016	3.250875	27.43233	1	2.2
16	03-2016	3.818534	205.7425	22	1.7
17	04-2016	4.912424	303.5176	82	1
18	05-2016	4.056247	216.6189	161	0.5
Sheet627

Cell Formulas
Range	Formula
G3	=SUMPRODUCT((MONTH(A2:A73)=3)*(E2:E73>=0.5)*D2:D73)
I3	=SUMPRODUCT((MONTH(A2:A73)=3)*(E2:E73>=0.5)*B2:B73)/SUMPRODUCT((MONTH($A$2:$A$73)=3)*($E$2:$E$73>=0.5))
H3	{=AVERAGE(IF(MONTH(A2:A73)=3,IF(E2:E73>=0.5,B2:B73)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[duggie33]

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]

Hi jtakw and Doug,

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

Cheers

 

[jtakw]

You're welcome.