sumif index match multiple columns

[dvsdasari]

Hi,


I have problem with sumif,index and match formula. My data is as follows


V.No. Particulars Debit Credit Category

1 test1 55 AP OFFICE

2 test2 60,000,000 CONTRA

3 test3 5,126 AP OFFICE

4 test4 120,603 TDS

100000 TDS


Here we are using the sumif,index and match formula to get total of TDS debit as follows......


=SUMIF(F24:F40,F28,INDEX(C24:F40,0,MATCH(D24,C24:F24,0)))


We need to deduct credit TDS total also with additional match like ............


=SUMIFS(F24:F40,F28,INDEX(C24:F40,0,MATCH(D24,C24:F24,0-MATCH(E24,C24:F24,0))))


But it is not give the actal answer, the answer is (Debit-120603-100000)=20603


Thanks in advance,

Sekhar

 

[Luke M]

I think you're making the formula too complicated.

You're use of INDEX and MATCH is very confusing...what is the "actual" problem you're trying to solve? It looks like you need the sum of credits for a category minus sum of debits for a category. In which case, it's something like:


=SUMIF(CategoryColumn,"TDS",CreditColumn)-SUMIF(CategoryColumn,"TDS",DebitColumn)