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

01-2015	0	0	0	0.6
02-2015	0	0	0	0.6
03-2015	4.59020672	251.971064	9	0.6
04-2015	3.36240616	106.422569	86	0.8
05-2015	4.81086566	205.664124	315	1
06-2015	1.73619817	49.2706227	17	1.2
07-2015	3.4600896	154.202966	11	1.5
08-2015	0.73493376	75.8961229	6	1.8
09-2015	4.8022825	50.2926116	5	2.1
10-2015	3.56508014	113.648239	21	2.4
11-2015	10.8861393	303.44733	60	2.5
12-2015	5.40441557	224.030724	27	2.6
01-2016	0	0	0	2.5
02-2016	3.25087488	27.4323336	1	2.2
03-2016	3.8185344	205.742502	22	1.7
04-2016	4.91242443	303.517583	82	1
05-2016	4.05624659	216.618883	161	0.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]

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.