sumproduct with 3 criteria - month, year and text

F0RE5T

Board Regular
Joined
Nov 4, 2014
Messages
201
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:

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
3,981
Office Version
365, 2019, 2016
Platform
Windows
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)))))
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,178
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

Board Regular
Joined
Nov 4, 2014
Messages
201
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

Board Regular
Joined
Nov 4, 2014
Messages
201
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:

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,080
Office Version
365
Platform
Windows
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

Board Regular
Joined
Nov 4, 2014
Messages
201
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:

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,080
Office Version
365
Platform
Windows
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

Board Regular
Joined
Nov 4, 2014
Messages
201
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

Board Regular
Joined
Nov 4, 2014
Messages
201
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))
 

Watch MrExcel Video

Forum statistics

Threads
1,099,891
Messages
5,471,325
Members
406,757
Latest member
dizzane

This Week's Hot Topics

Top