Match, Index problem

[TFranke]

I came across this problema (i thought it was really simple when I started) and after a few days I realize I can't solve it myself.
Of course the actual spreadsheet is larger both in columns and in lines, but the problem remains the same:

Having a list of employees, whose salary is divided in different Credit and Debit accounts, I need to know for a specific individual, the ammounts that add up for certain account types.

In the example below, There are two Credit accounts named "Discount" I need a formula to add those up matching the Account type (Credit in this case) and the Employee Name, Employee 2 in this example.

Bear in mind that the actual sheet has hundreds of accounts and hundreds of employees.

If there isn't a simple way to address this problem, please suggest alternatives.

Thank you in advance!!

Salary Problem.xlsx
	A	B	C	D
1	Credit		Discount	Discount
2	Debit	Account 1: Salary
3	Employee 1	50	80	32
4	Employee 2	20	30	48
5
6
7		Input Acct Type:	Credit
8		Input Acct Name	Discount
9		Employee 2	78
Sheet1

 

[awoohaw]

Would something like this solve your question. I'm not sure if I understand how your larger data set works.

Mr excel questions 55.xlsm
	A	B	C	D
1	Credit		Discount	Discount
2	Debit	Account 1: Salary
3	Employee 1	50	80	32
4	Employee 2	20	30	48
5
6
7		Input Acct Type:	Credit
8		Input Acct Name	Discount
9		Employee 2	78
10
11			78
TFranke

Cell Formulas
Range	Formula
C11	C11	=SUM(($B$1:$D$1=C8)*($A$3:$A$4=B9)*(B3:D4))

 

[TFranke]

Would something like this solve your question. I'm not sure if I understand how your larger data set works.

Mr excel questions 55.xlsm
	A	B	C	D
1	Credit		Discount	Discount
2	Debit	Account 1: Salary
3	Employee 1	50	80	32
4	Employee 2	20	30	48
5
6
7		Input Acct Type:	Credit
8		Input Acct Name	Discount
9		Employee 2	78
10
11			78
TFranke

Cell Formulas
Range	Formula
C11	C11	=SUM(($B$1:$D$1=C8)*($A$3:$A$4=B9)*(B3:D4))

Thank you for your answer. Allow me to do some further explanation. In your solution, you are not taking into account that the account is a Credit Account. So what I am looking for is a formula that will add (for example in the spreadsheet I provided) all cells that have the "Discount" Account name, AND that such name is in the Credit line.

Using your answer I tried =SUM(($A$1:$A$2=C7)*($B$1:$D$1=C8)*($A$3:$A$4=B9)*(B3:D4)) but it doesn't seem to work (and I cannot make my head around why it doesnt work.)

Why do I need this to work this way? Because sin some cases, I will have for some columns, account names in both the credit and debit lines, and I need the corresponding values to end up in different places depending if they are credit or debits.

 

[awoohaw]

The reason why it does not work is that you are trying to put a 1column x 2row maxtrix against a 3column x 2row matrix.
If you want to confirm in the calculation the first item in that row is "Credit" and then collect the columns with "Discount" that can be done like this.
BUT... I don't really see how it will help you repetitively as you will probably need to adjust the formula references constantly.

Just as a side note. Are you receiving your data in a file like this, or are you inputting this manually into a worksheet to then do the calculations?
The reason why I ask, is that there is probably a better way to aggregate your data and then do your reporting on a separate worksheet (but that is my pet peeve to never (or least not intersperse) manipulations of data within raw data.

Book2
	A	B	C	D
1	Credit		Discount	Discount
2	Debit	Account 1: Salary
3	Employee 1	50	80	32
4	Employee 2	20	30	48
5
6
7		Input Acct Type:	Credit
8		Input Acct Name	Discount
9		Employee 2	78
10
11		orig suggestion	78	=SUM(($B$1:$D$1=C8)*($A$3:$A$4=B9)*(B3:D4))
12		your formula	0	=SUM(($A$1:$A$2=C7)*($B$1:$D$1=C8)*($A$3:$A$4=B9)*(B3:D4))
13		updated suggestion	78	=SUM(($A$1=C7)*($B$1:$D$1=C8)*($A$3:$A$4=B9)*(B3:D4))
Sheet1

Cell Formulas
Range	Formula
C11	C11	=SUM(($B$1:$D$1=C8)*($A$3:$A$4=B9)*(B3:D4))
D11:D13	D11	=FORMULATEXT(C11)
C12	C12	=SUM(($A$1:$A$2=C7)*($B$1:$D$1=C8)*($A$3:$A$4=B9)*(B3:D4))
C13	C13	=SUM(($A$1=C7)*($B$1:$D$1=C8)*($A$3:$A$4=B9)*(B3:D4))

 

[TFranke]

Thank you very much! with this information I re-arranged my information and solved the problem using your formula.

Thank you!!!

 

[awoohaw]

My pleasure. I am happy found a solution at this forum.

Best wishes!