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?
[TABLE="width: 551"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Area[/TD]
[TD]Type of work[/TD]
[/TR]
[TR]
[TD]Utilities[/TD]
[TD]Gutters[/TD]
[/TR]
[TR]
[TD]Utilities[/TD]
[TD]crushed stone[/TD]
[/TR]
[TR]
[TD]Utilities[/TD]
[TD]crush barriers[/TD]
[/TR]
[TR]
[TD]Refrigerant[/TD]
[TD]Gutters[/TD]
[/TR]
[TR]
[TD]Refrigerant[/TD]
[TD]crushed stone[/TD]
[/TR]
[TR]
[TD]Refrigerant[/TD]
[TD]crush barriers[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 834"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD]UTILITIES. FGL[/TD]
[/TR]
[TR]
[TD]Utilities[/TD]
[TD]Excavation /
Разработка грунта[/TD]
[/TR]
[TR]
[/TR]
[TR]
[TD]Utilities[/TD]
[TD]Backfilling, leveling, compaction for slopes formation /
Обратная засыпка, планировка и уплотнение грунта[/TD]
[/TR]
[TR]
[/TR]
[TR]
[TD]Utilities[/TD]
[TD]Geogrid installation /
Устройство геогрида [/TD]
[/TR]
[TR]
[/TR]
[TR]
[TD]Utilities[/TD]
[TD]Install crushed stone 40-70 /
Устройство щебня 40-70[/TD]
[/TR]
[TR]
[/TR]
[TR]
[TD]Utilities[/TD]
[TD]Install crushed stone 20-40 /
Устройство щебня 20-40[/TD]
[/TR]
[TR]
[/TR]
[TR]
[TD]Utilities[/TD]
[TD]Geotextile laying /
Устройство геотекстиля [/TD]
[/TR]
[TR]
[/TR]
[TR]
[TD]Utilities[/TD]
[TD]Biomat installation /
Устройство биоматов[/TD]
[/TR]
[TR]
[/TR]
[TR]
[TD]Utilities[/TD]
[TD]Tiles installation /
Укладка тротуарной плитки [/TD]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]

 

[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.