2019-11-08
Excel - SUMIFS INDEX MATCH ISNUMBER SEARCH OR
stackoverflow
Question I'm quite stuck with a sumifs formula.

I need to bring the \$ values from Sheet 2 into Sheet 1 depending on the Ledger Account number.

However (here the problem), the sign of the \$ values has to follow this sign rule

``````**Sheet 1**                 **Sheet 2**                    Sign of \$
Assets                    Balance Sheet: Assets                +
Liabilities               Balance Sheet: Liabilities           -
Assets/Liabilities        Balance Sheet: Assets                +
Assets/Liabilities        Balance Sheet: Liabilities           -
Revenues                  Income Statement: Revenues           +
Costs                     Income Statement: Costs              -
``````

-------------------------------------------------------------------------------------------------------------------------

I'm editing the post for the seek of clarity and also because I have another issue related to the first one. I don't think it makes sense to create a new topic.

-------------------------------------------------------------------------------------------------------------------------

Part A has been solved thanks to @ScottCraner

``````=IF(ISNUMBER(SEARCH("Assets",INDEX(F:F,MATCH(B3,G:G,0)))),1,-1)*SUMIFS(H:H,G:G,B3)
``````

Part B has been solved thanks to the inputs of @ScottCraner

``````=IF(OR(ISNUMBER(SEARCH({"Assets","Revenues"},INDEX(F:F,MATCH(B3,G:G,0))))),1,-1)*SUMIFS(H:H,G:G,B3)
``````

Part C is where I'm stuck now. Formula B gives me + \$96.000,00 instead of \$32.000,00. The issue is that the formula sets the sign (+ or -) for the whole formula based on the first parameter it founds ("+" for "Assets" and "Revenues"; "-" for "Liabilities" and "Costs") when it should treat them independently and sum them accordingly.

1

In C3:

``````=IF(ISNUMBER(SEARCH("Assets",INDEX(F:F,MATCH(B3,G:G,0)))),1,-1)*SUMIFS(H:H,G:G,B3)
``````
1

In C3:

``````=IF(ISNUMBER(SEARCH("Assets",INDEX(F:F,MATCH(B3,G:G,0)))),1,-1)*SUMIFS(H:H,G:G,B3)
``````
Excel - SUMIFS INDEX MATCH ISNUMBER SEARCH OR
Related Documents