Find partial string in multiple cells and Sum next cell

[Eat More Bacon]

How can I search column A for any cell that contains Tylan and then Sum the results in column B?

 

[John Jairo V]

Hi @Eat More Bacon

If you want to get the C value into Column B when Column A contains "Tylan", you can use into B1:

=COUNTIF(A1;"*Tylan*")*C1

And drag it down.

But if you want to sum all the C values when Column A contains "Tylan", you can use in any cell:

=SUMIF(A1:A7,"*Tylan*",C1:C7)

Blessings!

 

[bosco_yip]

Sum for values in Column C, and search Column A that contains "Tylan", and put the result in column B

In B1, enter formula ( the result will return as same as John 's solution. )

=SUMIFS(C:C,A:A,"*Tylan*")

The difference between Sumif and Sumifs is :

• Sumif only work for single criteria
• Sumifs can work for single and multiple criteria, and is a newer function in replacement of Sumif

 

[Eat More Bacon]

Thanks! that works

 

[GraH - Guido]

Sum for values in Column C, and search Column A that contains "Tylan", and put the result in column B

In B1, enter formula ( the result will return as same as John 's solution. )

=SUMIFS(C:C,A:A,"*Tylan*")

The difference between Sumif and Sumifs is :

• Sumif only work for single criteria
• Sumifs can work for single and multiple criteria, and is a newer function in replacement of Sumif

In addition, SUMIFS is slightly more logic too. The first argument is the sum range, the following arguments are the if conditions. So it is SUM, IFS. In the s-less version it is the opposite and the sum range is at the end.

 

[Peter Bartholomew]

I have just found a spreadsheet that I didn't post. It assumes the med is always the second word of the text description and uses SUM and IF as separate functions. That gets around the limitation of SUMIFS in being limited to range references rather than arrays.

= LET(
      Word2ndλ,    LAMBDA(s, INDEX(TEXTSPLIT(s, " "),2)),
      SumIfλ,      LAMBDA(q,m, LAMBDA(m₀, SUM(IF(m=m₀,q)))),
      tblMed,      MAP(Table1[Med], Word2ndλ),
      tblQty,      Table1[Qty],
      distinctMed, UNIQUE(tblMed),
      quantity,    MAP(distinctMed, SumIfλ(tblQty,tblMed)),
      HSTACK(distinctMed, quantity)
  )

The procedure
1. defines a Lambda function to extract the second word;
2. defines a Lambda function to perform a conditional sum ;
3. extract meds as list from the table
4. identify associated quantities
5. form a separate list of distinct medication
6. apply conditional sum to each
7. form table showing distinct meds and quantities