Hi guys,
I'm working with a data set that I need to do some sumif type stuff with qualifications both on the rows and columns. This wonderful sight got me started with arrays, and I've gotten partway to my solution with an array. The below formula gets me part of the way, but now I'd like to add some qualifications to narrow the search.
First, for rows, I'd like to search for a word within the text, which is not always in the same spot. I got it working with Right(immediately after the sumif), but sometimes the qualifying text is somewhere in the middle.
Next, I'd like to add a Left search in addition to the above. I tried adding it after the Right function below, but it didn't work out.
SUM(IF(RIGHT('2017 Amt'!$A$7:$A$2000,6)=Data!$J229,IF(Data!$K$3='2017 Amt'!$C$6:$BL$6,'2017 Amt'!$C$7:$BL$2000)))
Thanks as always!
I'm working with a data set that I need to do some sumif type stuff with qualifications both on the rows and columns. This wonderful sight got me started with arrays, and I've gotten partway to my solution with an array. The below formula gets me part of the way, but now I'd like to add some qualifications to narrow the search.
First, for rows, I'd like to search for a word within the text, which is not always in the same spot. I got it working with Right(immediately after the sumif), but sometimes the qualifying text is somewhere in the middle.
Next, I'd like to add a Left search in addition to the above. I tried adding it after the Right function below, but it didn't work out.
SUM(IF(RIGHT('2017 Amt'!$A$7:$A$2000,6)=Data!$J229,IF(Data!$K$3='2017 Amt'!$C$6:$BL$6,'2017 Amt'!$C$7:$BL$2000)))
Thanks as always!
