# SUMIF Formula Help

1. ## SUMIF Formula Help

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.  Register To Reply

2. ## Re: SUMIF Formula Help

Try...

=SUMPRODUCT(--(MONTH(\$A\$2:\$A\$697)=1),--(\$M\$2:\$M\$697>=0.5),\$D\$2:\$D\$697)  Register To Reply

3. ## Re: SUMIF Formula Help

Hi Jeff,

Works fantastically, thanks! One more question, if I wanted to do the average rather than the sum for the above range what would the formula change to?

Thanks.  Register To Reply

4. ## Re: SUMIF Formula Help

=SUMPRODUCT(--(MONTH(\$A\$2:\$A\$697)=1),--(\$M\$2:\$M\$697>=0.5),\$D\$2:\$D\$697)/SUMPRODUCT(--(MONTH(\$A\$2:\$A\$697)=1),--(\$M\$2:\$M\$697>=0.5))  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 