SUMPRODUCT #Value! Error

[brncao]

Hello all,

I'm getting a #Value! error in the column "Result." Does anyone know what's the correct way to fix it? The formula evaluator is showing that the dates are where the error is occurring. I thought if the dates match, result should show 1, if not then 0.

SumProduct Error.xlsx
	A	B	C	D	E	F	G	H	I
1	Column1	Column2	Column3	Result		Column1	Column2	Column3	Extracted Date
2	1	A	1/1/2021	#VALUE!		1	A	SomeText 01/01/2021 On Text	1/1/2021
3	2	B	1/2/2021	#VALUE!		3	C	SomeText 01/02/2021 On Text	1/2/2021
4	3	C	1/3/2021	#VALUE!		4	D	SomeText 01/04/2021 On Text	1/4/2021
5	4	D	1/4/2021	#VALUE!		6	F	Mr. Excel	#VALUE!
6	5	E	1/5/2021	#VALUE!		7	G		#VALUE!
Sheet1

Cell Formulas
Range	Formula
D2:D6	D2	=SUMPRODUCT(--(Table2[Column1]=[@Column1]),--(Table2[Column2]=[@Column2]),--(Table2[Extracted Date],[@Column3]))
I2:I6	I2	=DATEVALUE(MID([@Column3],SEARCH(" On ",[@Column3])-10,10))

 

[Toadstool]

Hi Brncao,

You have two challenges:

1. If any Extracted Date is #VALUE (as are the last two in your example) then all Table1 Result will return #VALUE.
2. Your --(Table2[Extracted Date],[@Column3]) should be --(Table2[Extracted Date]=[@Column3])

 

[brncao]

Oops... However, after applying your fix, it's still showing #Value!. So I added IFERROR to Extracted Date to put "" in its place. That fixed it. Although, I'm curious if SUMPRODUCT can be forced to ignore all the errors and return 0.

Thanks!

 

[Toadstool]

Just making those two changes worked OK for me.
I assume you may have many more "Extracted Date" rows and that seems the most likely to give #VALUE so I'd wrap the IFERROR around that field and return a null if no date is found.

Brncao.xlsx
	A	B	C	D	E	F	G	H	I
1	Column1	Column2	Column3	Result		Column1	Column2	Column3	Extracted Date
2	1	A	01-Jan-21	1		1	A	SomeText 01/01/2021 On Text	01-Jan-21
3	2	B	02-Jan-21	0		3	C	SomeText 01/02/2021 On Text	02-Jan-21
4	3	C	03-Jan-21	0		4	D	SomeText 01/04/2021 On Text	04-Jan-21
5	4	D	04-Jan-21	1		6	F	Mr. Excel 01/05/2021 On duty	05-Jan-21
6	5	E	05-Jan-21	0		7	G	Mr. Excel 01/05/2021 On duty	05-Jan-21
Sheet1

Cell Formulas
Range	Formula
D2:D6	D2	=SUMPRODUCT(--(Table2[Column1]=[@Column1]),--(Table2[Column2]=[@Column2]),--(Table2[Extracted Date]=[@Column3]))
I2:I6	I2	=DATEVALUE(MID([@Column3],SEARCH(" On ",[@Column3])-10,10))

Named Ranges
Name	Refers To	Cells
Column1	=Sheet1!$F$2:$F$6	D2:D6
Column2	=Sheet1!$G$2:$G$6	D2:D6
Column3	=Sheet1!$H$2:$H$6	I2
Extracted_Date	=Sheet1!$I$2:$I$6	D2:D6

 

[brncao]

Thanks. Not all the records in Column3 of Table2 will have the keyword " ON ", which is what I want to ignore. This has now been solved!

 

[Toadstool]

OK.
=IFERROR(DATEVALUE(MID([@Column3],SEARCH("/",[@Column3])-2,10)),"")