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

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)

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

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

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)

Last edited: