• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Find partial string in multiple cells and Sum next cell

John Jairo V

Well-Known Member
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:
PHP:
=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:
PHP:
=SUMIF(A1:A7,"*Tylan*",C1:C7)
Blessings!
 

bosco_yip

Excel Ninja
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
80410
 

GraH - Guido

Well-Known Member
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

Well-Known Member
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.
Code:
= 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
80420
 

Attachments

Top