# SUMIF Help

#### drefiek1

##### New Member
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.

### 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
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
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.59021 251.971 9 0.6 04-2015 3.36241 106.423 86 0.8 05-2015 4.81087 205.664 315 1 06-2015 1.7362 49.2706 17 1.2 07-2015 3.46009 154.203 11 1.5 08-2015 0.734934 75.8961 6 1.8 09-2015 4.80228 50.2926 5 2.1 10-2015 3.56508 113.648 21 2.4 11-2015 10.8861 303.447 60 2.5 12-2015 5.40442 224.031 27 2.6 01-2016 0 0 0 2.5 02-2016 3.25087 27.4323 1 2.2 03-2016 3.81853 205.743 22 1.7 04-2016 4.91242 303.518 82 1 05-2016 4.05625 216.619 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.

#### jtakw

##### Well-known Member
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>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

#### duggie33

##### Board Regular

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
Hi jtakw and Doug,

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

Cheers

You're welcome.

Replies
2
Views
115
Replies
0
Views
70
Replies
3
Views
64
Replies
2
Views
43
Replies
6
Views
75