SumIfs question

hopeyb

New Member
Joined
Aug 7, 2019
Messages
2
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.

AsOfDateBankIdAccountNumberAccountNameBaiControlCurrencyTransaction Amount
6/3/2019'0430000961069907107facility name145USDACH Credits 657.44
6/3/2019'0430000961069907107facility name115USDLockbox Deposits 1,190.87
6/4/2019'0430000961069907107facility name575USDACH Credits 1,734.14
6/4/2019'0430000961077669217facility name475USDLockbox Deposits 1,247.00

<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

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!


<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Welcome to the Board!

Consider:


Book1
ABCDEFGHIJKLM
1AsOfDateBankIdAccountNumberAccountNameBaiControlCurrencyTransactionAmountDateAccountNameAmount
26/3/2019'0430000961069907107facility name145USDACH Credits657.446/3/2019facility name1848.31
36/3/2019'0430000961069907107facility name115USDLockbox Deposits1,190.87
46/4/2019'0430000961069907107facility name575USDACH Credits1,734.14
56/4/2019'0430000961077669217facility name475USDLockbox Deposits1,247.00
6
Sheet2
Cell Formulas
RangeFormula
M2=SUM(SUMIFS(H:H,A:A,K2,D:D,L2,G:G,{"ACH Credits","Lockbox Deposits"}))
 
Upvote 0
If you are interested, you can use a pivot table:


aff6ab412659bae42b13ffca1c86991c.jpg
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top