How to find string in a range them sum results from a different worksheet

I am trying to avoid using vba if I can but if it is the only solution I will. I have a range of cells containing different departments and I want to search through them and find only the options that contain "SE" in them. Then SUMIFS only those values from another page. I'm using office 2013

Worksheet 1

A	B	C	D
Green	Blue	Red	GreenSE

Worksheet 2

Dept	Sales
Green	5
GreenSE	10
Blue	2
BlueSE	3
Red	5
RedSE	6
Green	8
GreenSE	3

I want to find all options in worksheet 1 that contain SE (This list changes). Then SUM the corresponding values from worksheet 2.

E	F
SE's from List	13

CodePudding user response：

With Office 365 and later we can use FILTER inside a SUMIFS:

=SUM(SUMIFS(N:N,M:M,FILTER(A1:D1,ISNUMBER(SEARCH("SE",A1:D1)))))

Or as JVDV showed we can skip the SUMIFS with COUNTIFS inside the FILTER:

=SUM(FILTER(N2:N9,COUNTIFS(A1:D1,M2:M9,A1:D1,"*SE")))

With all versions:

=SUMPRODUCT(SUMIFS(N2:N9,M2:M9,"*SE",M2:M9,A1:D1))

CodePudding user response：

In older Excel it's quite hard to get what you want, but it's possible:

=(SUM(
  IFERROR(
    INDEX(Sheet2!$B$2:$B$9,
          AGGREGATE(15,6,
             ROW(Sheet2!$A$2:$A$9)-1
             /MMULT(--(TRANSPOSE(
                    INDEX(Sheet1!$A$1:$D$1,
                          AGGREGATE(15,6,                    
                             COLUMN(Sheet1!$A$1:$D$1)
                             /COUNTIFS(Sheet1!$A$1:$D$1,Sheet1!$A$1:$D$1,Sheet1!$A$1:$D$1,"*SE*"),                         
                          ROW($A$1:INDEX($A:$A,COUNTIF(Sheet1!$A$1:$D$1,"*SE*"))))))=Sheet2!$A$2:$A$9), 
             ROW($A$1:INDEX($A:$A,COUNTIF(Sheet1!$A$1:$D$1,"*SE*")))^0),
    ROW(Sheet2!$A$2:$A$9)-1)),
  0)))

The MMULT part filters the values containing SE (note this can also be in the middle or start of the value). It retrieves the row numbers of the values in Sheet2!A2:A9 that equal any of the filtered values. And this is used in the outer INDEX to get the values in column B summed.