sumproduct with 3 criteria - month, year and text

F0RE5T

Board Regular
Joined
Nov 4, 2014
Messages
204
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)
 
Last edited:
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.
 
Upvote 0

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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
 
Upvote 0
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"))


 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top