Help with Sumif Formula

[gmfston]

I am looking for help with a formula that will search for the dept (i.e. 10, 20) and Acct # (i.e. N40010) in a column anywhere on the page on sheet 2 and populate the values on sheet 1. The challenge is that the acct nos. on sheet 2 are not in the same columns (columns G and H) and are followed with account descriptions (i.e. N40010 . Med Rev-Self Pay). Note that on Sheet 1, the Acct # is in Col B and the account descriptions are in Col D. This is an abbreviated version of the project I am working on so assistance would be a huge time saver! Thank you for your help.

Sheet 1
[TABLE="width: 500"]
<tbody>[TR]
[TD]Col B[/TD]
[TD]Col C[/TD]
[TD]Col D[/TD]
[TD]Col E[/TD]
[/TR]
[TR]
[TD]Acct #[/TD]
[TD]Dept #[/TD]
[TD]Acct Description[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]N40010[/TD]
[TD]10[/TD]
[TD]Med Rev-Self Pay[/TD]
[TD]5,644[/TD]
[/TR]
[TR]
[TD]A40010[/TD]
[TD]10[/TD]
[TD]Adjustment-Self Pay[/TD]
[TD]-108[/TD]
[/TR]
[TR]
[TD]N41010[/TD]
[TD]20[/TD]
[TD]Den Rev-Self Pay[/TD]
[TD]7,667[/TD]
[/TR]
</tbody>[/TABLE]



Sheet 2
[TABLE="width: 500"]
<tbody>[TR]
[TD]Col G[/TD]
[TD]Col H[/TD]
[TD]Col M[/TD]
[TD]Col N[/TD]
[/TR]
[TR]
[TD]Acct No/Description[/TD]
[TD]Acct No/Description[/TD]
[TD]Dept #10[/TD]
[TD]Dept #20[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]N40010 . Med Rev-Self Pay[/TD]
[TD]5,644[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A40010 . Adj-Self Pay[/TD]
[TD][/TD]
[TD]-108[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]N41010 . Den Rev-Self Pay[/TD]
[TD][/TD]
[TD]7,667[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

Welcome to the Board!

Are the account numbers always exactly 6 characters long?

 

[Joe4]

Also, how many different departments do you actually have, and are they always exactly two digits long?

 

[Joe4]

If the following assumptions are correct:
- the data is on rows 2-4 of each sheet
- all account numbers are exactly six characters long
- there are only those two departments

Then place this formula in cell E2 of Sheet1 and copy down to row 3:

=SUMPRODUCT(--(LEFT(Sheet2!G$2:G$4,6)=B2),--(C$2:C$4=10),--(Sheet2!M$2:M$4))
+SUMPRODUCT(--(LEFT(Sheet2!G$2:G$4,6)=B2),--(C$2:C$4=20),--(Sheet2!N$2:N$4))
+SUMPRODUCT(--(LEFT(Sheet2!H$2:H$4,6)=B2),--(C$2:C$4=10),--(Sheet2!M$2:M$4))
+SUMPRODUCT(--(LEFT(Sheet2!H$2:H$4,6)=B2),--(C$2:C$4=20),--(Sheet2!N$2:N$4))

 

[gmfston]

Welcome to the Board!

Are the account numbers always exactly 6 characters long?

Hello Joe,

The accounts numbers vary in length. Some are 4 digits and others are seven digits long. Some contain an alpha character and some contain a decimal. For instance one account is 40264.7. There is another that is 7115-IK, another is 4415009. Thank you, Gina

 

[gmfston]

The departments range from two characters to four, unfortunately. Best, Gina

 

[Joe4]

So, it sounds like you are dealing with complex data that is formatted very poorly. This is really going to create a nightmare situation for you. I would not even fathom to think what a formula might look like (you see how complex the one I posted with the "simple assumptions" is).

If it were me, I would approach this very differently:
1. See if there is any way that you can receive the data on Sheet2 in a better format
2. If option 1 is not possible, I would work on creating a macro to simplify/standardize the data on Sheet2, so that it does the following:
- Combine columns G and H, and then splits the Description from the Account Number (so you have the Account Number by itself in a single column)
- List the department number in a single column
- List the amount in a single column

So, the data would be in a format that would then allow you to use a straightforward SUMIFS formula.

 

[gmfston]

So, it sounds like you are dealing with complex data that is formatted very poorly. This is really going to create a nightmare situation for you. I would not even fathom to think what a formula might look like (you see how complex the one I posted with the "simple assumptions" is).

If it were me, I would approach this very differently:
1. See if there is any way that you can receive the data on Sheet2 in a better format
2. If option 1 is not possible, I would work on creating a macro to simplify/standardize the data on Sheet2, so that it does the following:
- Combine columns G and H, and then splits the Description from the Account Number (so you have the Account Number by itself in a single column)
- List the department number in a single column
- List the amount in a single column

So, the data would be in a format that would then allow you to use a straightforward SUMIFS formula.

Thank you Joe. I appreciate your assistance. Happy New Year!

 

[Joe4]

You are welcome.
Happy New Year to you also.