Insert *data* from a cell in SUMIFS formula

[Pavel Dobrenkov]

Dear friends,

I need to use formula =SUMIFS() and sum the results from two columns from one table matching with other two columns in another table.

=SUMIFS('Active link'!$G:$G;'Active link'!$A:$A;$A4;'Active link'!$D:$D;"*crushed stone*")

But if for the first column it should match the whole word (no issue), for the second column i need a partial matching by using *text*.

Is it possible to enter instead of text the data from the cell, so it will be automatically changed for all the rows?

Area	Type of work
Utilities	Gutters
Utilities	crushed stone
Utilities	crush barriers
Refrigerant	Gutters
Refrigerant	crushed stone
Refrigerant	crush barriers

<colgroup><col><col></colgroup><tbody>
</tbody>

	UTILITIES. FGL
Utilities	Excavation / Разработка грунта
Utilities	Backfilling, leveling, compaction for slopes formation / Обратная засыпка, планировка и уплотнение грунта
Utilities	Geogrid installation / Устройство геогрида
Utilities	Install crushed stone 40-70 / Устройство щебня 40-70
Utilities	Install crushed stone 20-40 / Устройство щебня 20-40
Utilities	Geotextile laying / Устройство геотекстиля
Utilities	Biomat installation / Устройство биоматов
Utilities	Tiles installation / Укладка тротуарной плитки

<colgroup><col><col></colgroup><tbody>
</tbody>

 

[Pavel Dobrenkov]

Dear Friends,

I have found a solution myself not long ago, you just need to change the formula

=SUMIFS('Active link'!$G:$G;'Active link'!$A:$A;$A4;'Active link'!$D:$D;"*crushed stone*")

simply to: and everything works perfectly
=SUMIFS('Active link'!$G:$G;'Active link'!$A:$A;$A4;'Active link'!$D:$D;"*"&B4&"*")

and everything works perfectly

Happy Merry Christmas to everybody!!!

 

[Pavel Dobrenkov]

Unfortunately, i didn't find how to edit my post to make it more clear afterheads, if you want to understand this case to do the same in the future i will be glad to help you.