Auto fill cells depending on Drop down List selection

**Trengor** · 09-22-2008, 08:54 AM

I attached the table just to show what i was hoping to get the workbook to be able to total up for each day.

From the examples i have added in, it shows that i can total the amount of times each box has a selection using countif and then to just multiply the result by the amount paid for a total at the end of that row.

The red figures in the bottom right of each example day is what i am trying to get the spreadsheet to auto calculate as a sum of the three different payments for the drop down box selections.

i.e. i get £15 for working a 6 day week + £50 as i am in europe and not the UK + £15 for working an 8 hour day =£80

What i am hoping to do is remove all the result boxes from the bottom of the sheet and just have it auto adding the amount paid for each of the different drop downs and to display the total in each day.

I know it would be easier to just have the amounts as the drop downs but that means i got to remember the amounts for the different selections this way i can just enter how long and where.

I hope this explains it a bit better.

**NBVC** · 09-22-2008, 09:42 AM

Well, you can replace the formula in X16 with the following:

=COUNT(IF((MOD(COLUMN(B4:T12)-COLUMN(B4),3)=0)*ISNUMBER(SEARCH($F$15,B4:T12)),1))*30+COUNT(IF((MOD(COLUMN(B4:T12)-COLUMN(B4),3)=0)*ISNUMBER(SEARCH($F$15,B4:T12)),1))*0+COUNT(IF((MOD(COLUMN(B4:T12)-COLUMN(B4),3)=0)*ISNUMBER(SEARCH($F$15,B4:T12)),1))*15+COUNT(IF((MOD(COLUMN(B4:T12)-COLUMN(B4),3)=0)*ISNUMBER(SEARCH($F$15,B4:T12)),1))*30

and then you don't need the individual totals in F16, I16, L16, and O16

You can do similar in the the X cell sums... the formulas may be long though...

Basically the formula is a concatenation of several of these subformulas: COUNT(IF((MOD(COLUMN(B4:T12)-COLUMN(B4),3)=0)*ISNUMBER(SEARCH($F$15,B4:T12)),1))*30

which Counts if there is a match to what is in F15, in every 3 columns after column B. and multiplies the result by your multiple (e.g. 30 in this case).

Note: After you enter these formulas, they must be confirmed with CTRL+SHIFT+ENTER not just ENTER..where you will see { } brackets appear around it.

I attached your sheet showing the first formula in X16 with result you had.... you can delete the other stuff in Row 16 and the result should still remain.