Forumla to return a price based on three conditions:

[thewiserbeauty]

Hello all,

I am sort of facing a dilemma. At the end of every month, I have to prepare a billing file that totals all activity for each one of our clients. Unfortunately, this process takes me three days to complete because of all the data that is required and then reviewed.

I have managed to input a few formulas that have made this process a little easier for me; however, I cannot figure out how to create a formula that will generate a price based on account number, contract, and description.

Here is the tricky part for me:

Each one of our clients are on a contract.
Each of the contracts have different prices for the products we offer.

For example:
Clients
001 - Client 1 - Contract 2
002 - Client 2 - Contract 3
003 - Client 3 - Contract 1

Contract 1
Product 1: 2.00
Product 2: 4.00
Product 3: 6.00

Contract 2
Product 1: 3.00
Product 2: 6.00
Product 3: 9.00

In reference to the information included on the sample sheets:

- Account number is entered in C2
- Description is entered in E2; the format for the description (the data being billed) is date-company-product type(1, 2, or 3)
- Price to be generated in R2

Issue in play: C2 001 (Client 1 account number) that is on Contract 2 is being billed in E2 with date-company-product type 3 (product type 3 for 001) then potentially priced in R2 (9.00) automatically.

Can anyone help me with a formula that allows me to enter the account number, matching the contract type, to generate the correct pricing based on the product being billed in R2?

Thanks so much for the help everyone!

I hope I did this right!>.<

[AliGW]

Welcome to the forum.

Will you please attach a small sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[thewiserbeauty]

Thanks for the response! I realized I had to edit the sample, so I removed it then uploaded again! Thanks! Please let me know if there is anything else I need to fix.

[AliGW]

Are you open to changes in your data layout? The pricing tab layout is not as I would wish it to be to be able to do the data crunching easily.

[thewiserbeauty]

Are you open to changes in your data layout? The pricing tab layout is not as I would wish it to be to be able to do the data crunching easily.

Yes, sure. I’m not picky about the formatting or layout.

[thewiserbeauty]

Any solution available?

[thewiserbeauty]

Hello Ali,

Can you let me know if this is something possible to do?

If not, I'll just settle with doing everything like I have been.

[AliGW]

How do we know which client it is? We cannot work out the pricing unless we know the client. There is just an invoice number:

Excel 2016 (Windows) 32 bit

	B	C	D	E	R
1	TYPE	SOURCESYS.INVOICE#		DESCRIPTION	AMOUNT
2	INVOICE-SAMP	001		8/5/2019-HELLO, INC-PRODUCT 3	9.00

Sheet: Activity Report Sample

Will the description always be in the same format?

The layout change I suggested would be on the pricing sheet. I would arrange this in a normalised fashion thus:

Excel 2016 (Windows) 32 bit

	A	B	C
1	Contract 1	PRODUCT 1	2.00
2	Contract 1	PRODUCT 2	4.00
3	Contract 1	PRODUCT 3	6.00
4	Contract 2	PRODUCT 1	3.00
5	Contract 2	PRODUCT 2	6.00
6	Contract 2	PRODUCT 3	9.00
7	Contract 3	PRODUCT 1	4.00
8	Contract 3	PRODUCT 2	8.00
9	Contract 3	PRODUCT 3	12.00

Sheet: Contract_Prices Sample

[Glenn Kennedy]

I was totally confused, as you didn't show us your desired result!! So I've made a wild guess.

See sheet 1. Yellow cells: Chose a contract in E1. Choose a few products in A. Orange cells: enter a few quantities. Take a look and then show us what your expected results are and put them where you expect to see them