Sumifs to return a number from one of two columns

[tirztigg]

Below is a snip of the Data. I need a formula that looks for the number in Column D. Then if the number is in column F, return the sum as a negative number. If the number is in Column G, return the sum as a positive number. If this possible?

Example - # in Column D is "5703". return -12,163.83. / SAME formula for "5704" in column D would return 6,317,733.95.

I want the formula to look for a non-zero number in both Column F & G and then return the appropriate sum of the positive or negative number.

 

[Joe4]

Where exactly are you wanting to return these sums?

 

[tirztigg]

In a cell on a different tab on the same workbook. The snip of the data is on a tab called "FIRDPLUS".

Does that answer the question you are asking?

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Joe4]

In a cell on a different tab on the same workbook. The snip of the data is on a tab called "FIRDPLUS".

Does that answer the question you are asking?

Can you show us what that other tab looks like?
Are all the different IDs from column D already listed somewhere, or is there no data on that page and we need to list each one?

 

[Alex Blakenburg]

Are you sure something like this wouldn't work ?

This assumes you have Tran code in A5 of the other sheet and the formula is on Row 5

=-SUMIFS(FIRDPLUS!$F$3:$F$10000,FIRDPLUS!$D$3:$D$10000,$A5)+SUMIFS(FIRDPLUS!$G$3:$G$10000,FIRDPLUS!$D$3:$D$10000,$A5)

 

[tirztigg]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Thank you, I updated my account settings. MS 365. version 2205.

 

[tirztigg]

Can you show us what that other tab looks like?
Are all the different IDs from column D already listed somewhere, or is there no data on that page and we need to list each one?

The other tab doesn't any any of the trancode data. The pink cells are the ones I'm trying to write formulas in.

B2 is looking for tran code "6304"
B6 is looking for Tran Code "8410"
B10 is looking for Tran Code "5701" and "5724"
B23 is looking for Tran Code "5703" and "5726"
B40 is looking for Tran Code "5704". "5727". "5732", "5737"
B43 is looking for Tran Code "5702". "5725". "5730", "5735"

 

[Alex Blakenburg]

See if something likes this works for you.

20220609 Sumifs Multiple Criteria tirztigg.xlsx
	A	B	C	D
1				Info Received
2
3		0	CURRENCY (ATM)	B2 is looking for tran code "6304"
4
5
6		-1000	INTEREST PAYMENT - RESERVES	B6 is looking for Tran Code "8410"
7
8
9
10		-100	ACH DR ORIGINATION	B10 is looking for Tran Code "5701" and "5724"
11
12
13
14
15
16
17
18
19
20
21
22
23		10	ACH CR ORIGINATION	B23 is looking for Tran Code "5703" and "5726"
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40		20	ACH DEPOSITS (CR) RECEIVED	B40 is looking for Tran Code "5704". "5727". "5732", "5737"
41
42
43		-200	ACH WDL RECEIVED	B43 is looking for Tran Code "5702". "5725". "5730", "5735"
44
Data

Cell Formulas
Range	Formula
B3	B3	=-SUMIFS('FIRDPLUS!'!$F$3:$F$1000,'FIRDPLUS!'!$D$3:$D$1000,6304)+SUMIFS('FIRDPLUS!'!$G$3:$G$1000,'FIRDPLUS!'!$D$3:$D$1000,6304)
B6	B6	=-SUMIFS('FIRDPLUS!'!$F$3:$F$1000,'FIRDPLUS!'!$D$3:$D$1000,8410)+SUMIFS('FIRDPLUS!'!$G$3:$G$1000,'FIRDPLUS!'!$D$3:$D$1000,8410)
B10	B10	=-SUMIFS('FIRDPLUS!'!$F$3:$F$1000,'FIRDPLUS!'!$E$3:$E$1000,"*Debit*",'FIRDPLUS!'!$E$3:$E$1000,"*Originated*")+SUMIFS('FIRDPLUS!'!$G$3:$G$1000,'FIRDPLUS!'!$E$3:$E$1000,"*Debit*",'FIRDPLUS!'!$E$3:$E$1000,"*Originated*")
B23	B23	=-SUMIFS('FIRDPLUS!'!$F$3:$F$1000,'FIRDPLUS!'!$E$3:$E$1000,"*Credit*",'FIRDPLUS!'!$E$3:$E$1000,"*Originated*")+SUMIFS('FIRDPLUS!'!$G$3:$G$1000,'FIRDPLUS!'!$E$3:$E$1000,"*Credit*",'FIRDPLUS!'!$E$3:$E$1000,"*Originated*")
B40	B40	=-SUMIFS('FIRDPLUS!'!$F$3:$F$1000,'FIRDPLUS!'!$E$3:$E$1000,"*Credit*",'FIRDPLUS!'!$E$3:$E$1000,"*Received*")+SUMIFS('FIRDPLUS!'!$G$3:$G$1000,'FIRDPLUS!'!$E$3:$E$1000,"*Credit*",'FIRDPLUS!'!$E$3:$E$1000,"*Received*")
B43	B43	=-SUMIFS('FIRDPLUS!'!$F$3:$F$1000,'FIRDPLUS!'!$E$3:$E$1000,"*Debit*",'FIRDPLUS!'!$E$3:$E$1000,"*Received*")+SUMIFS('FIRDPLUS!'!$G$3:$G$1000,'FIRDPLUS!'!$E$3:$E$1000,"*Debit*",'FIRDPLUS!'!$E$3:$E$1000,"*Received*")

 

[tirztigg]

This worked!! Thank you so much!! I like what you did with B10 through B43 to pull by "description" instead of transcode. Much more flexible for when new codes are added (and they ae from time to time). This way I don't have to update the formula.