SUMIF or SUMPRODUCT with non-number values and partial match

[virtuosok]

Hi all. Post-processing a third party output which can't be changed, and would like to avoid helper columns.
I have set up the following formula. For blinding purposes only, I am adding (truncated path) insert below; in the actual formula there's a full and correct path:

=SUMPRODUCT('https://s(truncated path)status/[Study_Metrics.xlsx]Query Aging'!$AB$3:$AB$20000,--('https://s(truncated path)status/[Study_Metrics.xlsx]Query Aging'!$C$3:$C$20000="*"&"$A3)

It's not working as SUMPRODUCT doesn't support wild cards. I tried SUMIF and can't figure it out either.
In cell A3 I have a 3-digit number, 132
It is formatted as a number, too
In cells Query Aging'!$C$3:$C$20000 I have entries of that sort: 0132 - 0132 - Name Surname - Location Name
They have "General" format.
Finally, in Query Aging'!$AB$3:$AB$20000 I have a formula which returns either "" or a number. The output in this column is formatted as "General".
Any ideas please.

 

[Marcelo Branco]

Try something like...

=SUMPRODUCT(sum_range,--ISNUMBER(SEARCH("0"&A3,target_range)))
Replace sum_range and target_range accordingly your data

M.

 

[virtuosok]

Thanks Marcelo Branco!
I tried this

=SUMPRODUCT(sum_range,--ISNUMBER(SEARCH(“0”&A3,target_range)))

...and I'm getting 0. I should be getting 8. I also appended a wildcard after the reference to A3

=SUMPRODUCT(sum_range,--ISNUMBER(SEARCH(“0”&A3&"*",target_range)))

...still getting 0
How can I have the formula ignore the red piece in the reference range? E.g. 0132 - 0132 - Name Surname - Location Name
The “0”&A3 component within the formula should take care of the leading - green - zero, but the wild card is still not helping with the red piece that follows. I do have 132 in cell A3, formatted as number...

 

[Marcelo Branco]

I don't know how you are getting the data (???)

The formula worked for me with the data in the same workbook (example below)

Pasta1
	A	B	C	D
2	Criteria	Result	Text	Formulas
3	132	300	0132 - 0132 - Name Surname - Location Name	100
4			xxx	20
5			0132 - 0132 - Name Surname - Location Name	200
6			0132 - 0132 - Name Surname - Location Name
7			zzz	40
8
Plan9

Cell Formulas
Range	Formula
B3	B3	=SUMPRODUCT(D3:D7,--ISNUMBER(SEARCH("0"&A3,C3:C7)))
D4,D6:D7	D4	=IF(ISNUMBER(F4),F4,"")

M.

 

[virtuosok]

I don't know how, but this variant (without leading "0"&) works for me:
=SUMPRODUCT(sum_range,--ISNUMBER(SEARCH(A3,target_range)))
...and as such, the issue is solved

 

[Marcelo Branco]

..and as such, the issue is solved

Great that you found a solution

M.