SUMPRODUCT Issue

[Sam Walsh]

Hi All

I have an issue with a SUMPRODUCT formula I have used.

=SUMPRODUCT(('Feed Data'!A$79:A$128='Running Report Sept Onwards'!H123)*('Feed Data'!B$79:B$128='Running Report Sept Onwards'!J123)*('Feed Data'!D$79:D$128='Running Report Sept Onwards'!E123)*('Feed Data'!E$79:E$128='Running Report Sept Onwards'!N123)*('Feed Data'!F$79:F$128))

The formula is looking for essentially a sales price based on 4 other bits of information, the formula is picking up 90% of the values required but missing some in particular, no obvious reason.

Can anybody assist, any obvious errors?

Best

Sam

 

[Sam Walsh]

SCS Raw Data 2021.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
54	14/09/2021	05229	570071	12191 SCS - APPLICATION (WAL)	WP041 CENTRAL AREA	WP41-CENTRAL-VRCB-01	VRCB - VICTORIA RD CROSSOVER BOX	SCS RAILWAYS JV (WAL) - WET RAIL - HAULAGE ONLY	EPR/HB3804MD	HAULAGE TO WET	VRCB to WET	Walsh - CBDU93666	KR66JEJ	RT	1	Load	£ -	£ -	£ -	£ -	£ 90.00	£ 90.00	£ 90.00
55	14/09/2021	05231	570072	12191 SCS - APPLICATION (WAL)	WP041 CENTRAL AREA	WP41-CENTRAL-VRCB-01	VRCB - VICTORIA RD CROSSOVER BOX	SCS RAILWAYS JV (WAL) - WET RAIL - HAULAGE ONLY	EPR/HB3804MD	HAULAGE TO WET	VRCB to WET	Walsh - CBDU93666	KR66JEJ	RT	1	Load	£ -	£ -	£ -	£ -	£ 90.00	£ 90.00	£ 90.00
56	14/09/2021	05228	571273	12191 SCS - APPLICATION (WAL)	WP041 CENTRAL AREA	WP41-CENTRAL-VRCB-01	VRCB - VICTORIA RD CROSSOVER BOX	SCS RAILWAYS JV (WAL) - WET RAIL - HAULAGE ONLY	EPR/HB3804MD	HAULAGE TO WET	VRCB to WET	Walsh - CBDU93666	KR66JDZ	RT	1	Load	£ -	£ -	£ -	£ -	£ 90.00	£ 90.00	£ 90.00
57	14/09/2021	05230	571274	12191 SCS - APPLICATION (WAL)	WP041 CENTRAL AREA	WP41-CENTRAL-VRCB-01	VRCB - VICTORIA RD CROSSOVER BOX	SCS RAILWAYS JV (WAL) - WET RAIL - HAULAGE ONLY	EPR/HB3804MD	HAULAGE TO WET	VRCB to WET	Walsh - CBDU93666	KR66JDZ	RT	1	Load	£ -	£ -	£ -	£ -	£ 90.00	£ 90.00	£ 90.00
58	14/09/2021	06283	576367	12191 SCS - APPLICATION (WAL)	WP041 CENTRAL AREA	WP41-CENTRAL-VRCB-01	VRCB - VICTORIA RD CROSSOVER BOX	SCS RAILWAYS JV (WAL) - WET RAIL - HAULAGE ONLY	EPR/HB3804MD	HAULAGE TO WET	VRCB to WET	Walsh - CBDU93666	KR66JFF	RT	1	Load	£ -	£ -	£ -	£ -	£ 90.00	£ 90.00	£ 90.00
59	14/09/2021	06294	576368	12191 SCS - APPLICATION (WAL)	WP041 CENTRAL AREA	WP41-CENTRAL-VRCB-01	VRCB - VICTORIA RD CROSSOVER BOX	SCS RAILWAYS JV (WAL) - WET RAIL - HAULAGE ONLY	EPR/HB3804MD	HAULAGE TO WET	VRCB to WET	Walsh - CBDU93666	KR66JFF	RT	1	Load	£ -	£ -	£ -	£ -	£ 90.00	£ 90.00	£ 90.00
Running Report Sept Onwards

Cell Formulas
Range	Formula
D54:D59	D54	=IF(G54="","",VLOOKUP(G54,'Feed Data'!$1:$1048576, 2, FALSE))
E54:E59	E54	=IF($G54="","",VLOOKUP($G54,'Feed Data'!$1:$1048576, 3, FALSE))
F54:F59	F54	=IF($G54="","",VLOOKUP($G54,'Feed Data'!$1:$1048576, 4, FALSE))
K54:K59	K54	=IF(J54="","",(VLOOKUP(J54, 'Feed Data'!$A$17:$B$33,2,FALSE)))
L54:L59	L54	=IF(M54="","",'Feed Data'!A$66)
Q54:Q59	Q54	=SUMPRODUCT(('Feed Data'!A$79:A$128='Running Report Sept Onwards'!H54)*('Feed Data'!B$79:B$128='Running Report Sept Onwards'!J54)*('Feed Data'!C$79:C$128))
R54:R59	R54	=Q54*O54
S54:S59	S54	=SUMPRODUCT(('Feed Data'!A$79:A$128='Running Report Sept Onwards'!H54)*('Feed Data'!B$79:B$128='Running Report Sept Onwards'!J54)*('Feed Data'!D$79:D$128='Running Report Sept Onwards'!E54)*('Feed Data'!E$79:E$128='Running Report Sept Onwards'!N54)*('Feed Data'!F$79:F$128))
T54:T59	T54	=S54*O54
U54:U59	U54	=SUMPRODUCT(('Feed Data'!H$78:H$124='Running Report Sept Onwards'!K54)*('Feed Data'!I$78:I$124='Running Report Sept Onwards'!E54)*('Feed Data'!J$78:J$124='Running Report Sept Onwards'!N54)*('Feed Data'!K$78:K$124))
V54:V59	V54	=U54*O54
W54:W59	W54	=V54-T54-R54
I54:I59	I54	=IF(H54="","",VLOOKUP(H54, 'Feed Data'!$1:$1048576,2,FALSE))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C:C	Cell Value	duplicates	text	NO
C:C	Cell Value	duplicates	text	NO

Cells with Data Validation
Cell	Allow	Criteria
H54:H59	List	=#REF!

 

[Fluff]

Thanks for that, can you also post the relevant data from the Feed Data sheet as well.

 

[Sam Walsh]

SCS Raw Data 2021.xlsx
	A	B	C	D	E	F
126	WALSH - TILBURY DOCKS	0/4MM SHARP SAND	£ 15.00	WP041 CENTRAL AREA	8WT	£ 4.50
127	WALSH - TILBURY DOCKS	REJECT SAND	£ 8.00	WP041 CENTRAL AREA	8WT	£ 4.50
128	RMS - DAGENHAM	REJECT SAND	£ 8.00	WP041 CENTRAL AREA	8WT	£ 4.50
129	AGGREGATE INDUSTRIES - ACTON	170504 NON HAZ SOIL AND STONE DISPOSAL	£ 16.25	WP041 CENTRAL AREA	RT	£ 4.01
130	AGGREGATE INDUSTRIES - PURFLEET	170504 NON HAZ SOIL AND STONE TREATABLE	£ -	WP041 CENTRAL AREA	RT	£ 8.33
131	AUGEUN - KINGS CLIFFE	HAZ TREATABLE	£ 40.00	WP041 CENTRAL AREA	RT	£ 16.05
132	BRETTS - HITHERMORE	170504 NON HAZ SOIL AND STONE TREATABLE	£ 3.25	WP041 CENTRAL AREA	RT	£ 6.00
133	CAMDEN PLANT - CHINGORD	170504 NON HAZ SOIL AND STONE TREATABLE	£ 5.00	WP041 CENTRAL AREA	RT	£ 6.88
134	CAPPAGH - WEMBLEY	170101 INERT CONCRETE	£ -	WP041 CENTRAL AREA	RT	£ 5.90
135	CAPPAGH - WEMBLEY	170504 NON HAZ SOIL AND STONE TREATABLE	£ -	WP041 CENTRAL AREA	RT	£ 5.90
136	DAYS - BRENTFORD	170101 INERT CONCRETE	£ -	WP041 CENTRAL AREA	RT	£ 5.90
137	DAYS - BRENTFORD	170504 NON HAZ SOIL AND STONE TREATABLE	£ -	WP041 CENTRAL AREA	RT	£ 5.90
138	ENOVERT - SEVENOAKS	170504 NON HAZ SOIL AND STONE DISPOSAL	£ 3.50	WP041 CENTRAL AREA	RT	£ 12.50
139	FCC - BARKING	170504 NON HAZ SOIL AND STONE DISPOSAL	£ 14.30	WP041 CENTRAL AREA	RT	£ 6.50
140	FCC - BOW	170504 NON HAZ SOIL AND STONE DISPOSAL	£ 14.90	WP041 CENTRAL AREA	RT	£ 6.00
141	FCC - CRICKLEWOOD	170504 NON HAZ SOIL AND STONE DISPOSAL	£ 16.30	WP041 CENTRAL AREA	RT	£ 4.38
142	FM CONWAY - HEATHROW	170504 NON HAZ SOIL AND STONE TREATABLE	£ -	WP041 CENTRAL AREA	RT	£ 9.52
143	KELTBRAY - SILVERTOWN	170504 NON HAZ SOIL AND STONE DISPOSAL	£ 13.00	WP041 CENTRAL AREA	RT	£ 9.52
144	MICK GEORGE - MEPAL	HAZ TREATABLE	£ 40.00	WP041 CENTRAL AREA	RT	£ 16.05
145	MICK GEORGE - MEPAL	HAZ DISPOSAL	£ 40.00	WP041 CENTRAL AREA	RT	£ 16.05
146	O'MALLEY - HARPER LANE	170504 NON HAZ SOIL AND STONE TREATABLE	£ 50.00	WP041 CENTRAL AREA	RT	£ 6.02
147	O'MALLEY - HOLLYBUSH LANE	170504 NON HAZ SOIL AND STONE DISPOSAL	£ 250.00	WP041 CENTRAL AREA	RT	£ 6.02
148	SCS RAILWAYS JV (WAL) - WET RAIL - HAULAGE ONLY	HAULAGE TO WET	£ -	WP041 CENTRAL AREA	RT	£ 85.00
149	SOIL & WATER - WEXHAM PARK	170504 INERT SOILS STONES & CLAY	£ 7.00	WP041 CENTRAL AREA	RT	£ 6.02
150	SOIL & WATER - WEXHAM PARK	170504 NON HAZ SOIL AND STONE DISPOSAL	£ 14.17	WP041 CENTRAL AREA	RT	£ 6.02
151	TARMAC/GRS - TYTTENHANGER	170504 INERT SOILS STONES & CLAY	£ 7.00	WP041 CENTRAL AREA	RT	£ 6.02
152	THAMES - DAGENHAM	170504 INERT SOILS STONES & CLAY	£ 8.33	WP041 CENTRAL AREA	RT	£ 6.02
153	VEOLIA - BEACONSFIELD	170504 NON HAZ SOIL AND STONE DISPOSAL	£ -	WP041 CENTRAL AREA	RT	£ 8.33
Feed Data

Cell Formulas
Range	Formula
C150	C150	=255/18
C152	C152	=150/18

 

[Sam Walsh]

SCS Raw Data 2021.xlsx
	A	B	C	D	E	F
126	WALSH - TILBURY DOCKS	0/4MM SHARP SAND	£ 15.00	WP041 CENTRAL AREA	8WT	£ 4.50
127	WALSH - TILBURY DOCKS	REJECT SAND	£ 8.00	WP041 CENTRAL AREA	8WT	£ 4.50
128	RMS - DAGENHAM	REJECT SAND	£ 8.00	WP041 CENTRAL AREA	8WT	£ 4.50
129	AGGREGATE INDUSTRIES - ACTON	170504 NON HAZ SOIL AND STONE DISPOSAL	£ 16.25	WP041 CENTRAL AREA	RT	£ 4.01
130	AGGREGATE INDUSTRIES - PURFLEET	170504 NON HAZ SOIL AND STONE TREATABLE	£ -	WP041 CENTRAL AREA	RT	£ 8.33
131	AUGEUN - KINGS CLIFFE	HAZ TREATABLE	£ 40.00	WP041 CENTRAL AREA	RT	£ 16.05
132	BRETTS - HITHERMORE	170504 NON HAZ SOIL AND STONE TREATABLE	£ 3.25	WP041 CENTRAL AREA	RT	£ 6.00
133	CAMDEN PLANT - CHINGORD	170504 NON HAZ SOIL AND STONE TREATABLE	£ 5.00	WP041 CENTRAL AREA	RT	£ 6.88
134	CAPPAGH - WEMBLEY	170101 INERT CONCRETE	£ -	WP041 CENTRAL AREA	RT	£ 5.90
135	CAPPAGH - WEMBLEY	170504 NON HAZ SOIL AND STONE TREATABLE	£ -	WP041 CENTRAL AREA	RT	£ 5.90
136	DAYS - BRENTFORD	170101 INERT CONCRETE	£ -	WP041 CENTRAL AREA	RT	£ 5.90
137	DAYS - BRENTFORD	170504 NON HAZ SOIL AND STONE TREATABLE	£ -	WP041 CENTRAL AREA	RT	£ 5.90
138	ENOVERT - SEVENOAKS	170504 NON HAZ SOIL AND STONE DISPOSAL	£ 3.50	WP041 CENTRAL AREA	RT	£ 12.50
139	FCC - BARKING	170504 NON HAZ SOIL AND STONE DISPOSAL	£ 14.30	WP041 CENTRAL AREA	RT	£ 6.50
140	FCC - BOW	170504 NON HAZ SOIL AND STONE DISPOSAL	£ 14.90	WP041 CENTRAL AREA	RT	£ 6.00
141	FCC - CRICKLEWOOD	170504 NON HAZ SOIL AND STONE DISPOSAL	£ 16.30	WP041 CENTRAL AREA	RT	£ 4.38
142	FM CONWAY - HEATHROW	170504 NON HAZ SOIL AND STONE TREATABLE	£ -	WP041 CENTRAL AREA	RT	£ 9.52
143	KELTBRAY - SILVERTOWN	170504 NON HAZ SOIL AND STONE DISPOSAL	£ 13.00	WP041 CENTRAL AREA	RT	£ 9.52
144	MICK GEORGE - MEPAL	HAZ TREATABLE	£ 40.00	WP041 CENTRAL AREA	RT	£ 16.05
145	MICK GEORGE - MEPAL	HAZ DISPOSAL	£ 40.00	WP041 CENTRAL AREA	RT	£ 16.05
146	O'MALLEY - HARPER LANE	170504 NON HAZ SOIL AND STONE TREATABLE	£ 50.00	WP041 CENTRAL AREA	RT	£ 6.02
147	O'MALLEY - HOLLYBUSH LANE	170504 NON HAZ SOIL AND STONE DISPOSAL	£ 250.00	WP041 CENTRAL AREA	RT	£ 6.02
148	SCS RAILWAYS JV (WAL) - WET RAIL - HAULAGE ONLY	HAULAGE TO WET	£ -	WP041 CENTRAL AREA	RT	£ 85.00
149	SOIL & WATER - WEXHAM PARK	170504 INERT SOILS STONES & CLAY	£ 7.00	WP041 CENTRAL AREA	RT	£ 6.02
150	SOIL & WATER - WEXHAM PARK	170504 NON HAZ SOIL AND STONE DISPOSAL	£ 14.17	WP041 CENTRAL AREA	RT	£ 6.02
151	TARMAC/GRS - TYTTENHANGER	170504 INERT SOILS STONES & CLAY	£ 7.00	WP041 CENTRAL AREA	RT	£ 6.02
152	THAMES - DAGENHAM	170504 INERT SOILS STONES & CLAY	£ 8.33	WP041 CENTRAL AREA	RT	£ 6.02
153	VEOLIA - BEACONSFIELD	170504 NON HAZ SOIL AND STONE DISPOSAL	£ -	WP041 CENTRAL AREA	RT	£ 8.33
Feed Data

Cell Formulas
Range	Formula
C150	C150	=255/18
C152	C152	=150/18

This is just a small snapshot of the information, hopefully enough

 

[Fluff]

Thanks for that, the 1st thing that strikes me, is that your formulae in Q & S are looking at rows 79 to 128, but it looks as though your data extends well below that.

 

[Sam Walsh]

?, it extends to 228, typo on my original formulas dragged through!

I feel somewhat stupid now!

Thank you for spotting that!

 

[ebea]

SCS Raw Data 2021.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
54	14/09/2021	05229	570071	12191 SCS - APPLICATION (WAL)	WP041 CENTRAL AREA	WP41-CENTRAL-VRCB-01	VRCB - VICTORIA RD CROSSOVER BOX	SCS RAILWAYS JV (WAL) - WET RAIL - HAULAGE ONLY	EPR/HB3804MD	HAULAGE TO WET	VRCB to WET	Walsh - CBDU93666	KR66JEJ	RT	1	Load	£ -	£ -	£ -	£ -	£ 90.00	£ 90.00	£ 90.00
55	14/09/2021	05231	570072	12191 SCS - APPLICATION (WAL)	WP041 CENTRAL AREA	WP41-CENTRAL-VRCB-01	VRCB - VICTORIA RD CROSSOVER BOX	SCS RAILWAYS JV (WAL) - WET RAIL - HAULAGE ONLY	EPR/HB3804MD	HAULAGE TO WET	VRCB to WET	Walsh - CBDU93666	KR66JEJ	RT	1	Load	£ -	£ -	£ -	£ -	£ 90.00	£ 90.00	£ 90.00
56	14/09/2021	05228	571273	12191 SCS - APPLICATION (WAL)	WP041 CENTRAL AREA	WP41-CENTRAL-VRCB-01	VRCB - VICTORIA RD CROSSOVER BOX	SCS RAILWAYS JV (WAL) - WET RAIL - HAULAGE ONLY	EPR/HB3804MD	HAULAGE TO WET	VRCB to WET	Walsh - CBDU93666	KR66JDZ	RT	1	Load	£ -	£ -	£ -	£ -	£ 90.00	£ 90.00	£ 90.00
57	14/09/2021	05230	571274	12191 SCS - APPLICATION (WAL)	WP041 CENTRAL AREA	WP41-CENTRAL-VRCB-01	VRCB - VICTORIA RD CROSSOVER BOX	SCS RAILWAYS JV (WAL) - WET RAIL - HAULAGE ONLY	EPR/HB3804MD	HAULAGE TO WET	VRCB to WET	Walsh - CBDU93666	KR66JDZ	RT	1	Load	£ -	£ -	£ -	£ -	£ 90.00	£ 90.00	£ 90.00
58	14/09/2021	06283	576367	12191 SCS - APPLICATION (WAL)	WP041 CENTRAL AREA	WP41-CENTRAL-VRCB-01	VRCB - VICTORIA RD CROSSOVER BOX	SCS RAILWAYS JV (WAL) - WET RAIL - HAULAGE ONLY	EPR/HB3804MD	HAULAGE TO WET	VRCB to WET	Walsh - CBDU93666	KR66JFF	RT	1	Load	£ -	£ -	£ -	£ -	£ 90.00	£ 90.00	£ 90.00
59	14/09/2021	06294	576368	12191 SCS - APPLICATION (WAL)	WP041 CENTRAL AREA	WP41-CENTRAL-VRCB-01	VRCB - VICTORIA RD CROSSOVER BOX	SCS RAILWAYS JV (WAL) - WET RAIL - HAULAGE ONLY	EPR/HB3804MD	HAULAGE TO WET	VRCB to WET	Walsh - CBDU93666	KR66JFF	RT	1	Load	£ -	£ -	£ -	£ -	£ 90.00	£ 90.00	£ 90.00
Running Report Sept Onwards

Cell Formulas
Range	Formula
D54:D59	D54	=IF(G54="","",VLOOKUP(G54,'Feed Data'!$1:$1048576, 2, FALSE))
E54:E59	E54	=IF($G54="","",VLOOKUP($G54,'Feed Data'!$1:$1048576, 3, FALSE))
F54:F59	F54	=IF($G54="","",VLOOKUP($G54,'Feed Data'!$1:$1048576, 4, FALSE))
K54:K59	K54	=IF(J54="","",(VLOOKUP(J54, 'Feed Data'!$A$17:$B$33,2,FALSE)))
L54:L59	L54	=IF(M54="","",'Feed Data'!A$66)
Q54:Q59	Q54	=SUMPRODUCT(('Feed Data'!A$79:A$128='Running Report Sept Onwards'!H54)*('Feed Data'!B$79:B$128='Running Report Sept Onwards'!J54)*('Feed Data'!C$79:C$128))
R54:R59	R54	=Q54*O54
S54:S59	S54	=SUMPRODUCT(('Feed Data'!A$79:A$128='Running Report Sept Onwards'!H54)*('Feed Data'!B$79:B$128='Running Report Sept Onwards'!J54)*('Feed Data'!D$79:D$128='Running Report Sept Onwards'!E54)*('Feed Data'!E$79:E$128='Running Report Sept Onwards'!N54)*('Feed Data'!F$79:F$128))
T54:T59	T54	=S54*O54
U54:U59	U54	=SUMPRODUCT(('Feed Data'!H$78:H$124='Running Report Sept Onwards'!K54)*('Feed Data'!I$78:I$124='Running Report Sept Onwards'!E54)*('Feed Data'!J$78:J$124='Running Report Sept Onwards'!N54)*('Feed Data'!K$78:K$124))
V54:V59	V54	=U54*O54
W54:W59	W54	=V54-T54-R54
I54:I59	I54	=IF(H54="","",VLOOKUP(H54, 'Feed Data'!$1:$1048576,2,FALSE))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C:C	Cell Value	duplicates	text	NO
C:C	Cell Value	duplicates	text	NO

Cells with Data Validation
Cell	Allow	Criteria
H54:H59	List	=#REF!

In your formula in D54, E54 and F54, are missing references, where to Lookup. It's one of the things. I guess it should have been D1:F10000

 

[Fluff]

You're welcome & thanks for the feedback.

I feel somewhat stupid now!

We've all been there, it's an easy mistake to make.