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)

 

[AlanY]

atm, the formula works out the number of rows between G5 and G304.

what's in H$5?

=SUMPRODUCT(--(MONTH(Assets!$J$5:$J$304=[COLOR="#FF0000"]MONTH(1&H$5)[/COLOR]*(YEAR(Assets!$J$5:$J$304=$H$3)*(Assets!$H$5:$H$304="Yes")*(Assets!$G$5:$G$304)))))

 

[Aladin Akyurek]

Is this what you are after?

=SUMPRODUCT(

Assets!$G$5:$G$304,--(

Assets!$J$5:$J$304-DAY(

Assets!$J$5:$J$304)+1=$H$5-DAY($H$5)+1),--(

Assets!$H$5:$H$304="Yes"))

where H5 houses a date value.
​

​

​

​

 

[F0RE5T]

Hi
Thanks or the reply,

H$5 is the month i.e April (in this format
H$3 is the year i.e. 2019 (in this format

Cheers
Forest

 

[F0RE5T]

Is this what you are after?

=SUMPRODUCT(

Assets!$G$5:$G$304,--(

Assets!$J$5:$J$304-DAY(

Assets!$J$5:$J$304)+1=$H$5-DAY($H$5)+1),--(

Assets!$H$5:$H$304="Yes"))

where H5 houses a date value.
​

​

​

​

Hi many thanks for the reply,

********Sorry but have just seen that the formula column J should be I ********
I am getting a #VALUE error though.

H5 and H3 are drop down box lists hence using the (1&H$5) . I know this works as other fields find the correct data but use an index and match because the way the data required for that is stored
If it helps below is the "Data Table" of the searched data. H$5 and H$3 are on a different sheet as mentioned at the top. I have only shown the data I am searching to save confusion.



[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A

[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]£225

[/TD]
[TD]Yes
[/TD]
[TD]05/04/2019

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]£225

[/TD]
[TD]Sold
[/TD]
[TD]04/04/201
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


In his data example I would expect to return £225 -- the formula is looking for 'Month 4', and 'Year 19) an he word 'Yes'

Does this help?

Cheers

Forest

 

[steve the fish]

What aladin gave will work if you dont ignore the crucial part of where H5 houses a date. You can convert April and 2019 into a date by using:

=0+(1&H5&H3)

Check that it produces a date then replace the H5 in aladins formula with 0+(1&$H$5&$H$3). Obviously attach sheet name if required.

 

[F0RE5T]

What aladin gave will work if you dont ignore the crucial part of where H5 houses a date. You can convert April and 2019 into a date by using:

=0+(1&H5&H3)

Check that it produces a date then replace the H5 in aladins formula with 0+(1&$H$5&$H$3). Obviously attach sheet name if required.

Hi many thanks for the reply

I did change the incorrect column reference 'J to I' and still get the #Value error.


Just for my education, why are you wanting to change the part that converts the 'April' and '2019' as I know that element works.


I did a test on your edit as a standalone and it brings the serial number back for the 01/04/2019 but remember I am ignoring the day element of the month so to equal your suggestion would surely bring back a miss match??


My way of using the year/month date does work the example below brings back the correct result
e.g. =SUMPRODUCT(--(MONTH('Outgoings Reg'!$B$4:$B$303)=MONTH(1&H$5)*(YEAR('Outgoings Reg'!$B$4:$B$303)=$H$3)),INDEX('Outgoings Reg'!$G$4:$X$303,0,MATCH($C24,'Outgoings Reg'!$G$2:$X$2,0)))

many thanks and always looking to learn.

Forest

 

[steve the fish]

What aladins formula is doing is converting your date to the first of the month then converting your range to test to dates that are all the first of the month. Just replace h5 in aladins formula with the date I suggested and tey that.

 

[F0RE5T]

What aladin gave will work if you dont ignore the crucial part of where H5 houses a date. You can convert April and 2019 into a date by using:

=0+(1&H5&H3)

Check that it produces a date then replace the H5 in aladins formula with 0+(1&$H$5&$H$3). Obviously attach sheet name if required.

Hi

I tried the above as a standalone formula to see if returns a date. I tried with both date and general formatting on the cell and it still brings back #value ?

I have entered it in the same sheet as H5 &H3 so just entered what you typed. To clarify I did not include it in aladin formula
I hate my ignorance at times LOL

Cheers
Forest

 

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