2019-11-08
Excel - SUMIFS INDEX MATCH ISNUMBER SEARCH OR
stackoverflow
Question

enter image description here

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.

Answer
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
See more ...