SumIfs question

[hopeyb]

Hello,

I am trying to figure out how to create a formula that will feed into a different worksheet. I have most of it figured out but I am stuck at two parts.

[TABLE="width: 832"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]AsOfDate[/TD]
[TD]BankId[/TD]
[TD]AccountNumber[/TD]
[TD]AccountName[/TD]
[TD]BaiControl[/TD]
[TD]Currency[/TD]
[TD]Transaction[/TD]
[TD] Amount [/TD]
[/TR]
[TR]
[TD="align: right"]6/3/2019[/TD]
[TD]'043000096[/TD]
[TD="align: right"]1069907107[/TD]
[TD]facility name[/TD]
[TD="align: right"]145[/TD]
[TD]USD[/TD]
[TD]ACH Credits[/TD]
[TD] 657.44[/TD]
[/TR]
[TR]
[TD="align: right"]6/3/2019[/TD]
[TD]'043000096[/TD]
[TD="align: right"]1069907107[/TD]
[TD]facility name[/TD]
[TD="align: right"]115[/TD]
[TD]USD[/TD]
[TD]Lockbox Deposits[/TD]
[TD] 1,190.87[/TD]
[/TR]
[TR]
[TD="align: right"]6/4/2019[/TD]
[TD]'043000096[/TD]
[TD="align: right"]1069907107[/TD]
[TD]facility name[/TD]
[TD="align: right"]575[/TD]
[TD]USD[/TD]
[TD]ACH Credits[/TD]
[TD] 1,734.14[/TD]
[/TR]
[TR]
[TD="align: right"]6/4/2019[/TD]
[TD]'043000096[/TD]
[TD="align: right"]1077669217[/TD]
[TD]facility name[/TD]
[TD="align: right"]475[/TD]
[TD]USD[/TD]
[TD]Lockbox Deposits[/TD]
[TD] 1,247.00[/TD]
[/TR]
</tbody>[/TABLE]

I want to be able to pull the amounts as of the date so it feeds into the correct cell based on the account name. The sum has to be the total of ach credits and lockbox credits. there are also other items in the transaction column ( debits, checks paid) so i only need those two items to be based off the sum. I tried the sumifs and i can get it to work with only one criteria but not both for the transaction. (i hope this makes sense) Any help is appreciated!

[TABLE="width: 832"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

Welcome to the Board!

Consider:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	AsOfDate	BankId	AccountNumber	AccountName	BaiControl	Currency	Transaction	Amount			Date	AccountName	Amount
2	6/3/2019	'043000096	1069907107	facility name	145	USD	ACH Credits	657.44			6/3/2019	facility name	1848.31
3	6/3/2019	'043000096	1069907107	facility name	115	USD	Lockbox Deposits	1,190.87
4	6/4/2019	'043000096	1069907107	facility name	575	USD	ACH Credits	1,734.14
5	6/4/2019	'043000096	1077669217	facility name	475	USD	Lockbox Deposits	1,247.00
6
Sheet2

Cell Formulas
Range	Formula
M2	=SUM(SUMIFS(H:H,A:A,K2,D:D,L2,G:G,{"ACH Credits","Lockbox Deposits"}))

 

[DanteAmor]

If you are interested, you can use a pivot table: