sumproduct with 3 criteria - month, year and text

[F0RE5T]

Hi
Many thanks in advance for any assistance.

I have a table of assets on a sheet called "Assets" and collating various totals on a sheet called "Mthly Bal & P & L" In the formula below $H$3 and $H$4 are on this sheet and the formula is also stored on this sheet in cell G11.

When an asset is sold the following is stored

Col G holds sold price, Col H holds text "Sold" value, Col J holds date sold (dd/mm/yyyy)

I am using the following formula to calculate the total value of items sold in a specified month of a specified year

=SUMPRODUCT(--(MONTH(Assets!$J$5:$J$304=MONTH(1&H$5)*(YEAR(Assets!$J$5:$J$304=$H$3)*(Assets!$H$5:$H$304="Yes")*(Assets!$G$5:$G$304)))))

For initial testing purposes I have registered just 1 asset See below, and if the formula had worked would increase the test data to create various scenarios.
A5 b5 C5 D5 E5 F5 G5 H5 I5 J5
ServInv1 Dell Monitor Prince 01/04/2019 £1.00 4.0 £225.00 Sold 04/04/2019 05/05/2019

I am showing this because the formula brings back a value of "£300" ???

Can anyone tell me where I have gone wrong.. I am using sumproduct in other calculations and working great but this is a headache

Many thanks for any and all replies

Forest (UK)

 

[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))


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.

 

[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

 

[Aladin Akyurek]

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"))
​

​