CODEWITHSUNDEEP
excelexcel-formula
acr
acr

Reputation: 1746

EXCEL : INDIRECT with LEFT Function

In my workbook, I have sheet called DayWise2019. I am trying to check if B Column text is SBI-O and C column first two character is DB. If that matching I am getting the sum of column D to another sheet 2019 D5 cell.

Here the problem is I am unable to use left formula to check first two character in C

If I use below formula in 2019 D5 cell, its calculating values correctly.

=SUMIFS(INDIRECT("'" & ShortCodes!$H$20 & "'!" & "D" &D$2):INDIRECT("'" & ShortCodes!$H$20 & "'!" & "D" &D$3),INDIRECT("'" & ShortCodes!$H$20 & "'!" & "B" &D$2):INDIRECT("'" & ShortCodes!$H$20 & "'!" & "B" &D$3),"SBI-O",INDIRECT("'" & ShortCodes!$H$20 & "'!" & "C" &D$2):INDIRECT("'" & ShortCodes!$H$20 & "'!" & "C" &D$3),"DB-BAN")

But as you can see, I have hardcoded "DB-BAN" above. If I replace that with a left function like below

=SUMIFS(INDIRECT("'" & ShortCodes!$H$20 & "'!" & "D" &D$2):INDIRECT("'" & ShortCodes!$H$20 & "'!" & "D" &D$3),INDIRECT("'" & ShortCodes!$H$20 & "'!" & "B" &D$2):INDIRECT("'" & ShortCodes!$H$20 & "'!" & "B" &D$3),"SBI-O",INDIRECT("'" & ShortCodes!$H$20 & "'!" & "C" &D$2):INDIRECT("'" & ShortCodes!$H$20 & "'!" & "C" &D$3),LEFT(INDIRECT("'" & ShortCodes!$H$20 & "'!" & "C" &D$2):INDIRECT("'" & ShortCodes!$H$20 & "'!" & "C" &D$3),6))

Is there anyway to resolve it ?

Sample Sheet

Upvotes: 1

Views: 1045

Answers (1)

user4039065
user4039065

Reputation:

Try¹,

=SUMIFS(INDIRECT(TEXT(ShortCodes!$H$20, "'@'!\D")&D$2&":D"&D$3),
        INDIRECT(TEXT(ShortCodes!$H$20, "'@'!\B")&D$2&":B"&D$3), "SBI-O",
        INDIRECT(TEXT(ShortCodes!$H$20, "'@'!\C")&D$2&":C"&D$3), "DB-BAN*")

Using "DB-BAN*" as the criteria is the same as comparing the six left-most characters to "DB-BAN".

¹ Yes, you can use different lines in the formula bar to line up important sections of a formula without detriment.

Upvotes: 1

Related Questions