Find a value in a table given a specific customer reference.

[Hitman51]

I am trying to put a formula into a sheet that is pulling data from another sheet within the same workbook. Ultimately, I am trying to calculate the gross margin based on a specific customer. The sheet I am putting the formula into has a cell with a customer name in it that can be changed to various customers. I need the formula to review the data sheet, find this customer from a specific column, and then calculate the gross margin based on that.

I can calculate the total margin of all customers easily, but I need it to calculate it based on specific customers.

My existing formula is:
=SUMIF('Install Log'!E4:E1000,C4)*'Install Log'!AL4:AL1000/'Install Log'!D4:D1000

1) Install Log E4:E1000 = the range on the data spreadsheet that has various customer names
2) C4 = the location on the other spreadsheet where the customer name can be input
3) the remaining part of the formula is the calculation of Gross margin (sum of all Profits/sum of all revenue)

Any help would be appreciated. Thanks!

[Richard Buttrey]

Unfortunately your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

Since you're a newcomer I'll change the title on this occasion but please note for the future.

This will require a VLOOKUP() or perhaps a INDEX() MATCH() combination.

[Hitman51]

Thank you Richard. I will be more aware of that when I post again.

Do you have any recommendations on the formula? When I used Vlookup to identify the customer, how then do I take the sum of a column and divide it by another sum of a Column to show the results only for that specific customer identified?

[Richard Buttrey]

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there.

[Hitman51]

I have attached a simple file to show what I am trying to do. In the Margin tab, I am trying to calculate the gross margin based on the info for that customer in the data tab (all values related to that customer in Profit Column/all values related to that customer in the revenue column).

[Hitman51]

After much trial and error, I was able to solve the issue. The formula that I used was as follows (using the spreadsheet uploaded for the example).

=XLOOKUP(B1,Data!A2:A11,Data!C2:C11)/XLOOKUP(B1,Data!A2:A11,Data!B2:B11)