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:

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
atm, the formula works out the number of rows between G5 and G304.

what's in H$5?

Code:
=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)))))
 
Upvote 0
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.

 
Upvote 0
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
 
Upvote 0
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.



A

B
C
D
E
F
G
H
I
J
5
£225

Yes
05/04/2019

6
£225

Sold
04/04/201

<tbody>
</tbody>


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
 
Last edited:
Upvote 0
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.
 
Upvote 0
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
 
Last edited:
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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))
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

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