We help IT Professionals succeed at work.

I need an Excel Formul

wrt1mea
wrt1mea asked
on
I have a spreadsheet I am trying to total up costs per accounting code per company. The spreadsheet has 3 tabs; 1st Tab is Master and it contains all of the data that has been imported from various invoices. 2nd tab is the name of the company I want to total every accounting code they bill too. 3rd tab is the same with a different company I am totaling.

Keep in mind there will be approximately 100 entries like you see on the Master Sheet. I need each companys tab to list the company name, accounting number, and the total charged to that accounting number. Pay close attention because the accouting codes change.

See attached and good luck!
11-2-11.xlsx
Comment
Watch Question

Looks somewhat like a homework assignment to me...

Author

Commented:
Nope...not a homework assignment.

I am trying to sum up accounting costs for the admin assitant at work so she isnt spending 3 days adding up data to invoice.
Certainly strikes a somewhat imperious tone with the "pay close attention" and "good luck".

@Write1mea: What have you tried?

Author

Commented:
LOL, man...I dont know what to say. Its not homework. I am sitting in my office now.

I put the pay close attention because of the accounting code going 1000 1001 1002 1003 1009 1010, etc

The good luck part is because I thought it was a nice thing to say.
BRONZE EXPERT
Most Valuable Expert 2013

Commented:
Hello Ted,

Try this formula in C2 for each company worksheet

=SUMIFS(MASTER!I:I,MASTER!B:B,A2,MASTER!E:E,B2)

copied down column

regards, barry
Well assuming it's really not homework, which I have an incredibly hard time believing especially since the data in the fields and the names ACME and TNT seem pretty common with all homework I saw back in school... you are looking at a pretty complicated function to look through the data and then output more data to new tabs.

Someone here might be able to help you figure this out... maybe ask the teach... errrrr, google?
Are the accounting codes the same by company from month-to-month or does the solution need to parse out the relevant codes at each instance?  Is there a specific set of accounting codes in use?  How many different companies tabs are there presently?

Barry's solution is the right way to go if the accounting codes are static each time and if the number of companies are relatively small.  If they vary (or may in the future), you may need a solution with a bit more AI that can parse out the accounting codes and build the tabs as needed.  Quite a bit more involved at that point.  Respond with answers to these questions if you need a more robust solution.

Author

Commented:
The accounting codes will vary month to month. I need the accounting code and the company's name from the Master sheet to pull over to the company's tab, and then sum up its accounting code charges.Currently there are 7 companies and I dont think there will be any more than that. So bulding each tab is no biggie.

Barry's solution works great! But I need it to pull over any and all accounting codes for each company as well as the company's name. Thanks for the help Barry!
I would assume that the possible list of accounting codes is the same for each company.

What you could do is simply list all possible accounting codes on each company tab and copy the formula down.  Some may be zero if that code wasn't encountered, but the results could be re-sorted to float the relevant stuff to the top.

Author

Commented:
Ah hah! Good idea. You are right about the accounting codes being the same for each company. To give you more understanding, there are 7 contract companies that work for a Client company. I am working with the Client company to help sort out costs, etc from the contract companies.

The only thing is there are approximately 1500 accounting codes. I know not much for excel to handle. But I definitely need to make sure it only sums them up per company per accounting code.
BRONZE EXPERT
Most Valuable Expert 2013
Commented:
OK, my formula above gives you the figures in column C - if you want formula to generate all the data, including the other two columns then try like this

in A2 this formula for the sheet name

=REPLACE(CELL("filename",A1),1,FIND("]",CELL("filename",A1)),"")

then in A3 copied down as far as required

=IF(B3<>"",A2,"")

Then in B2

=IFERROR(INDEX(MASTER!E$2:E$100,SMALL(IF(MASTER!B$2:B$100=A$2,IF(COUNTIF(B$1:B1,MASTER!E$2:E$100)=0,ROW(MASTER!A$2:A$100)-ROW(MASTER!A$2)+1)),1)),"")

confirmed with CTRL+SHIFT+ENTER and copied down

and then amended formula in C2

=IF(A2="","",SUMIFS(MASTER!I:I,MASTER!B:B,A2,MASTER!E:E,B2))

also copied down

I did the same for TNT

You can copy one sheet and just change the sheet name to the relevant company and it will pick up all the data from master sheet

I copied formulas down to row 20 (and assumed that data in master sheet would go to row 100) . change both as required, see attached

regards, barry
27428323.xlsx
Definitely into some programming at this point.  I'm afraid I don't have the time to develop a solution at this time, but maybe one of the other Experts can assist.  If you need a "for now" solution, I would say to go ahead and import all the accounting codes and copy the formula, then show the admin assistant how to sort the column containing the values.  Providing you can readily get an electronic list of the codes, it shouldn't take more than 15 minutes to create one generic company tab, then copy it and rename them as needed.

Author

Commented:
Barry...you are simply amazing and the IT world is better off because of you. Thanks for all of your help past and present and look for more questions from me in the future. I wont ask for a tutorial this time

Thanks for knowing that IT WAS NOT A HOMEWORK ASSIGNMENT! :)

Works Perfectly!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Good show, Barry

Author

Commented:
Mark, thanks for helping!
No worries...sorry for the initial skepticism  ;)
BRONZE EXPERT
Most Valuable Expert 2013

Commented:
Actually, better formula to replace the long one in B2 an down - this one is shorter and doesn't need "array entry"

=IFERROR(INDEX(MASTER!E$2:E$100,MATCH(1,INDEX((MASTER!B$2:B$100=A$2)*(COUNTIF(B$1:B1,MASTER!E$2:E$100)=0),0),0)),"")

see revised attachment

barry
27428323v2.xlsx

Author

Commented:
GREAT! Thanks again Barry.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.