# Thread: sumproduct with 3 criteria - month, year and text Thanks: 0 Likes: 0

1. ## Re: sumproduct with 3 criteria - month, year and text

Hi

I have just worked on the following and though I haven't included the check on "Yes" at the moment it works.. have I gone crazy or you agree

=SUMPRODUCT((MONTH(Assets!\$I\$5:\$I\$305)=MONTH(1&H\$5))*(YEAR(Assets!\$I\$5:\$I\$305)=(\$H\$3))*(Assets!\$G\$5))

noting that the year is a number in a drop down list but the month is a text in a drop down list so I changed the way H\$3 is handled.

2. ## Re: sumproduct with 3 criteria - month, year and text

Originally Posted by F0RE5T
Hi

I have just worked on the following and though I haven't included the check on "Yes" at the moment it works.. have I gone crazy or you agree

=SUMPRODUCT((MONTH(Assets!\$I\$5:\$I\$305)=MONTH(1&H\$5))*(YEAR(Assets!\$I\$5:\$I\$305)=(\$H\$3))*(Assets!\$G\$5))
Hi

I have tested the code below with changing year, month and "Yes" to other text and the values returned are perfect.....

Many thanks for your support as I have learnt a lot.

Forest

3. ## Re: sumproduct with 3 criteria - month, year and text

Originally Posted by F0RE5T
Hi

I have tested the code below with changing year, month and "Yes" to other text and the values returned are perfect.....

Many thanks for your support as I have learnt a lot.

Forest
=Assets!\$G\$5 * SUMPRODUCT(--(Assets!\$I\$5:\$I\$305-DAY(Assets!\$I\$5:\$I\$305)+1=DATE(\$H\$3,\$H\$5,1)))

If you the Yes condition also add:

=Assets!\$G\$5 * SUMPRODUCT(--(Assets!\$I\$5:\$I\$305-DAY(Assets!\$I\$5:\$I\$305)+1=DATE(\$H\$3,\$H\$5,1)),--(Assets!\$H\$5:\$H\$304="Yes"))