Account & Dept Mapping

Maybe you can use sumproduct in this way... =SUMPRODUCT((Account={Acct 1,Acct 2,Acct 3})*(Dept={Dept 1,Dept 2,Dept 3})*Amount)
I've tried something similar but it may need to have a semicolon on the second set ={Dept 1;Dept 2;Dept 3}

what I gave you is not an array formula, you don't need to hit ctrl/shift/enter to use it. It is hard to give you a better formula from just a quick post without any real details.
If you are dealing with changing details maybe a pivot table or using power query or possible VBA.

Try this array formula in C3:

Formula:

Please Login or Register  to view this content.

Because it is an array formula you have to finish with Ctrl + Shift + Enter. Then copy down.

I altered the sumproduct to your numbers but for now I am not sure based on the way you structured the data how to make one that looks at the left and right in the output tab under depts.

I'm returning your workbook with a couple different formulas and with a pivot table that you can adjust in a new tab.

looking at Jacc's, I think you have to change A12 and B12 to A3 and B3.

well, Jacc's looks like it solves it for you as tested and adjusting from A12 B12 to A3 and B3 then activated with ctrl/shift/enter. I'd go with that one as it looks like you'll need fewer adjustments.

Maybe I misunderstood something. My formula in the attached workbook is in C12.
In C12 it sums only the values who's account matches for instance the value in A12 (via the mapping table) and who's department can be found in cell B12.

Edit: Oh, it worked for you, how nice!

You are welcome!

Just a small disclaimer, it only works as long as all accountnumbers have the same number of digits (in this case 2 diigits). The day someone decides to mix 2-digit account numbers and 3-digit accountnumbers it may find false matches and fail miserably. Hopefully this is not an issue for you.

The challenge is that the account numbers are in the same cell, hence I have to use the SEARCH function. SEARCH will just look for the character 5, it doesn't care if that 5 is alone or if it's part of 45.

I don't see a smooth solution to that. Possibly have the account cell both start and end with commas like ",21,10," instead of just "21,10". That way i can make the search for ",5," instead of just "5". I think it will work but it will look awkward. Interested?

Yes you should but you have to change the format to text obviously. Or just add an ' in front of the 05. This sign becomes invisible and works the same as formatting as text.

Wow, 20 000 rows. How long does it take to calculate?

I thought Jacc's solution was the best but now wondering if it is starting to look like maybe the sumproduct or sum/sumifs solution might be better?

I'm still not able to get the formula to work properly for some reason. BUT, when I got to work this morning, the light bulb over my hear turned on and the solution was obvious. What I did instead was create the two tables that I needed on a separate sheet. And on the data source, I just did a vlookup and set the account numbers to the proper cost type and the departments to the proper department group. Then on my output tab, I just used sumifs for the cost type and department group that i needed. Worked perfectly. I don't know why I didn't think of this yesterday.

Thank you both for all your help!

You're welcome for what I did. Sometimes it is best to just rearrange the data to make the process work better. Glad you figured out a way to accomplish what you needed.