# getting the value of the cell that has a forumla and avoid circular warnings

1. ## getting the value of the cell that has a forumla and avoid circular warnings

I have a table that I had set up to figure out the amount that will cost for a certain user. The amount will change depending on the number of time this item is chosen (i.e. one item cost \$60 and if 5 people chose this item, it will cost \$12 per person because the cost is split between them). Then I want to have the sum of the amount added up.

Take a look here: http://prnt.sc/eo3tlb for what I have so far.

P5 and Q5 are the items A and B wants to order. The cost is split between them for a total of \$30.50 per person. # of request will let me know simply how many people are requesting for this item.

What I want to do is instead of having the "x" in P5 and Q5, it turns into the cost of the item so I can do a sum (seen in P1 and Q1) of the total cost for all items for each person.

The only issue is each time i try different things, I either get an error or a circular warning. I get why I'm getting a circular warning but I don't know how to get around it.

For the # of request, I have this code: =COUNTA(C5:Q5) and for the Cost, I have =61/(COUNTA(C5:Q5). Any ideas on how I can get the value of the cost on the cell it's counting?

I've tried searching for a solution but nothing has worked so far.

Any help would be appreciated. Thanks!

2. ## Re: getting the value of the cell that has a forumla and avoid circular warnings

You will be better off posting an example file for us/someone to use/work on.

3. ## Re: getting the value of the cell that has a forumla and avoid circular warnings

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly 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 solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. 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.

4. ## Re: getting the value of the cell that has a forumla and avoid circular warnings

Sorry! First time posting. Thanks for the instructions. I wasn't sure what to do next after uploading but here's the link I grabbed from the upload:

https://www.excelforum.com/attachmen...1&d=1490625505

Hope this works and thanks for your help!

5. ## Re: getting the value of the cell that has a forumla and avoid circular warnings

In O1, copied across

=SUMIF(O\$3:O\$100,"x",\$S\$3:\$S\$100)

6. ## Re: getting the value of the cell that has a forumla and avoid circular warnings

I think I may be doing something wrong. I put it in the first column (in this case M) and changed the O to M and S to Q to match the two columns in question, but it's coming up as a value of "0".

7. ## Re: getting the value of the cell that has a forumla and avoid circular warnings

Leave the S as S... that's the column you're trying to add up.

8. ## Re: getting the value of the cell that has a forumla and avoid circular warnings

Am I doing something wrong?

9. ## Re: getting the value of the cell that has a forumla and avoid circular warnings

When I opened the file attached to post #8, Glen's formula had been pasted into M3 and copied down.
Glen's instructions said to paste the formula into row one and copy across.
Please follow these instructions to correct the file attached to post #8:
1) Select cell M1,
2) Delete the SUM formula currently shown in the formula bar
3) Paste Glen's formula, =SUMIF(M\$3:M\$100,"x",\$Q\$3:\$Q\$100) into M1,
4) Pull the fill handle for cell M1 across to cell O1,
5) Select the range M3:M15 and press the delete key,
6) Select the range N3:P3 and press the delete key,
7) Select cell P4 and pull the fill handle up to cell P3.
Let us know if you have any questions.

10. ## Re: getting the value of the cell that has a forumla and avoid circular warnings

That was it! Thank you for explaining it. I apparently was having the case of the Monday and it didn't click. Thanks for all your help. This is exactly what I need.

11. ## Re: getting the value of the cell that has a forumla and avoid circular warnings

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.

12. ## Re: getting the value of the cell that has a forumla and avoid circular warnings

Dohh!! Stoopid mistake by yours truly...

There are currently 1 users browsing this thread. (0 members and 1 guests)