Search code examples
excelexcel-formulasumifs

Sum x if y string contains a or b or c - Excel

I have data set A that contains revenue per professional and data set B of professionals/team groups. I want to sum the revenue in data set A based on the groupings in data set B in excel. The actual lists include hundreds of rows.

Looks like sumif may work but I'm struggling with the multiple cases if one group has x members and another has x + 1. Max grouping is 7 members.

Dataset A looks like this:

Name       | Revenue
----------------------
John B.    | 100
John Doe   | 50
Michael J. | 80
Lucy G.    | 1000 
John Q.    | 20
Alex B.    | 50
Curt T.    | 50

Dataset B looks like this

Professionals | Members
-----------------------
Team John     | John B., John Doe, John Q.
Michael J.    | Michael J.
Lucy G.       | Lucy G.
Alex & Curt   | Alex B., Curt T.

Expected Result

Professionals | Members                   | Revenue
----------------------------------------------------
Team John     | John B., John Doe, John Q.| 170
Michael J.    | Michael J.                | 80
Lucy G.       | Lucy G.                   | 1000
Alex & Curt   | Alex B., Curt T.          | 100
Solution

  • Use FILTERXML to return an array:

    =SUMPRODUCT(SUMIFS(B:B,A:A,FILTERXML("<a><b>"&SUBSTITUTE(F2,",","</b><b>")&"</b></a>","//b")))

    enter image description here