discontinuous selection

[markusbohu]

This is driving me a bit crazy. How do I make a discontinuous selection that I can copy into multiple rows. Here is what I want to do, and what I tried so far:

Basically, I want to enter a dollar amount in the "Tip Pool" column and then I want to be able to put a "x" on the left side of each person who should get a portion of that amount.

In the "ppl" column (G) I therefore need to count how many "x" there are in this row, so that the columns for each person (I,K,M,O,...) then can divide the "Tip Pool" by the number of people participating.

Originally I tried to just put a =COUNTIF(H3:Z3,"x") into the ppl cell, but when I then refer to that cell in the calculation of cell I3, Excel complains about a circular reference, because I3 is in the H3:Z3 range.

SO: I need to use a discontinuous range in G3. You can see the formula that works for this in the formula bar up top, but because that formula uses hard-coded references, when I now copy that formula into the cell range G4:G2000 (I need a lot of rows) it uses the hard-coded references and does not increase the line number; so you can see that cells G4, G5, etc. also count the "x"-es in row 3, rather than their own row :(

So: I tried starting out with something like: =SUM(COUNTIF(INDIRECT({"H"&ROW(A3),...
BUT Excel does not seem to allow a formula and cell reference inside the "{" in the INDIRECT function.

So I can't turn the list "H3","J3",...  into a list where the "3" is a relative number, denoting the current line.

This is driving me crazy! It doesn't seem that what I want to do is that unusual. How can I make it so my employees, who aren't well-versed in excel (or math) can simply enter a total amount in a row, then check of who should get a share of the amount and have excel put in the correct number for each person--and do it in a single row, so I can have 2000 rows ready for them to do the same thing (and keep a historical record)

Thanks for any tips! (no pun intended)

[#2 · Accepted solution]

@markusbohu The circular reference warning comes up because the simple COUNTIF formula that would calculate pay-out, sits within the range that the formula is addressing. But, in this case the pay-out will never affect the outcome of the COUNTIF, so you need to tell Excel that it should use iterative calculation. You'll find it under Excel, Preferences..., Formulas and Lists, Calculation. Tick the box and leave the other settings as they are. Now everything will work as you want it to. Have tested it with the attached workbook. Note that the COUNTIF can be written so that it looks at the entire row. That way, you can add people without ever having to expand the range.

[#3]

@Riny_van_Eekelen Thank you. That is a much simpler solution than the one I came up with. The only disadvantage is that it depends on a global setting, which does not save with the excel file itself, so I wonder what happens when the file gets opened on a computer where this setting has not been adjusted.
Yesterday I came up with a MUCH more complicated solution:
I simplified the countif formula to: =SUM(COUNTIF(INDIRECT(BC3:BY3),"x"))
I put ROW($A3) in BB3, put numbers that increase by 2 in line 2 (starting with the ascii code of the letter of the first column with "x"

and in the BC3:BY3 range I put in something like: =CHAR(BL$2)&$BB3 (and ="A"&CHAR(BM$2)&$BB3)

Then I hid the entire BB to BY column range and protected the sheet.

It works, but does not have the additional advantage of being able to easily add more employees, so I had to make sure I had plenty of columns available for them. My intention is to then hide the columns for each inactive or terminated employee, so they don't have to scroll.