SUMIF'S and OR statement assistance

[DJFSG]

Hello,

I Need a formula not a macro to help with this, I have tried SUMIF's and OR combination but am having issues,
My workbook as two tabs (1) Data (2) Template
Data Tab has in Col A - D and rows 1 - 7

Journal Amount	Employee Last Name	Cost Center	Account String
441.6​	Smith	99-555	5-9-4-1355
38.23​	Smith	24-500	5-9-4-1350
40​	Smith	99-555	5-9-4-1354
7.54​	Smith	22-700	7-9-4-1300
15.97​	Reed	28-600	6-9-4-1300
37.61​	Reed	98-555	5-9-4-1353

I need formula to change the Cost Center and Account String based on this logic.

If Cost center starts with 21, 24 - 30, 98 it will need to be changed to 99-100

and Acct String will need to be changed to 1-0-0-71xx (where xx = first two digits of cost center)

Template tab should show this in Col A - D and rows 1-7

Journal Amount	Employee Last Name	Cost Center	Account String
441.6​	Smith	99-555	5-9-4-1355
38.23​	Smith	99-100	1-0-0-7124
40​	Smith	99-555	5-9-4-1354
7.54​	Smith	99-100	1-0-0-7122
15.97​	Reed	99-100	1-0-0-7128
37.61​	Reed	99-100	1-0-0-7198

Any assistance would be greatly appreciated and thank you in advance.

Regards,
Doug

 

[Eric W]

Welcome to the MrExcel forum!

Try:

Book1
	A	B	C	D
1	Journal Amount	Employee Last Name	Cost Center	Account String
2	441.6	Smith	99-555	5-9-4-1355
3	38.23	Smith	99-100	1-0-0-7124
4	40	Smith	99-555	5-9-4-1354
5	7.54	Smith	22-700	7-9-4-1300
6	15.97	Reed	99-100	1-0-0-7128
7	37.61	Reed	99-100	1-0-0-7198
Template

Cell Formulas
Range	Formula
A2:D7	A2	=LET(a,Data!$A$2:$D$7,b,LEFT(INDEX(a,,3),2)+0,c,ISNUMBER(MATCH(b,{21,24,25,26,27,28,29,30,98},0)),CHOOSE({1,2,3,4},INDEX(a,,1),INDEX(a,,2),IF(c,"99-100",INDEX(a,,3)),IF(c,"1-0-0-71"&b,INDEX(a,,4))))
Dynamic array formulas.

 

[DJFSG]

=LET(a,Data!$A$2:$D$7,b,LEFT(INDEX(a,,3),2)+0,c,ISNUMBER(MATCH(b,{21,24,25,26,27,28,29,30,98},0)),CHOOSE({1,2,3,4},INDEX(a,,1),INDEX(a,,2),IF(c,"99-100",INDEX(a,,3)),IF(c,"1-0-0-71"&b,INDEX(a,,4))))

Eric, Thank you, this is slick. I have to try to modify it so that it can be copied down to match the actual number of rows of data that changes each time we use this process. Still working through the logic you used to make this occur.

 

[Eric W]

Here's one way:

Book1
	A	B	C	D
1	Journal Amount	Employee Last Name	Cost Center	Account String
2	441.6	Smith	99-555	5-9-4-1355
3	38.23	Smith	99-100	1-0-0-7124
4	40	Smith	99-555	5-9-4-1354
5	7.54	Smith	22-700	7-9-4-1300
6	15.97	Reed	99-100	1-0-0-7128
7	37.61	Reed	99-100	1-0-0-7198
8
Template

Cell Formulas
Range	Formula
A2:D7	A2	=LET(a,FILTER(Data!$A$2:$D$1000,Data!$B$2:$B$1000<>""),b,LEFT(INDEX(a,,3),2)+0,c,ISNUMBER(MATCH(b,{21,24,25,26,27,28,29,30,98},0)),CHOOSE({1,2,3,4},INDEX(a,,1),INDEX(a,,2),IF(c,"99-100",INDEX(a,,3)),IF(c,"1-0-0-71"&b,INDEX(a,,4))))
Dynamic array formulas.

For the a, section of the formula, just pick a row that's well below the maximum number of rows you might have, the the FILTER will remove any blank lines. The rest of the formula is the same. Let me know if you have any other questions.

Glad I could help!

 

[DJFSG]

Thank you, it's working perfectly.