Lozzy 20:53 19 Apr 05
Locked

I have been asked to create an expense spreadsheet where Column A and B are date and text. Column C needs to be where you insert 1 through to 23 (representing an expense Code reference) with the value going into Column D. How do I create the formula that if the user inserts which ever expense code it will appear in column F and continually add the figures automatically. So for example if the user type in Line 1 code 1 and 20.00 for meals it auto goes in Column F then he also adds in Line 10 per say, another meal, that cost is auto added to the existing figure in Column F being the cumulative figure for the month.

I hope that this can be done. Any help would be much appreciated. I have basic knowledge of Excel not advanced.

VoG II 21:03 19 Apr 05

I hope I've understood this correctly, Lozzy. If you just want Column F to contain a running total of expenses, irrespectice of the code in column C then:

Assuming that you have titles in row 1 and the data starts in row 2 then in F2 enter the formula:

=SUM(D\$2:D2)

and press Enter. Then hover the cursor over the bottom right of cell F2 until it turns into a +. Hold down the left mouse button and drag down as far as required. This will copy the formula down, for example in F4 it should read

=SUM(D\$2:D4)

and give a running total.

Lozzy 21:17 19 Apr 05

I understand what you have said, question,

if the user enters in the code 1 = meals or what ever code from the 23 what would the formula be so that the correct value gets inserted into the correct line. For example, If column C represents the expense code and column D is the value and Column F has the cumulative balance for each code what would the formula be to ensure code 1 through to 23's value gets added to the correct cell in Column F. Per say Column F line 2 = Meals Line 3 = Tools I need to ensure that when the user types in the code it gets added to the correct line in column F.

I hope I have not complicated this. AHHHHHH Its doing my head in!!!!

VoG II 21:30 19 Apr 05

You need to restructure your sheet slightly. You need to have a column with the possible codes 1-23 then another column that contains formulas like (I've restricted this to 8 rows, you may well need more):

=SUMIF(C1:C8,"=1",D1:D8)

=SUMIF(C2:C9,"=2",D2:D8)

=SUMIF(C3:C10,"=3",D3:D8)

I hope this helps.

Harish-194666 21:44 19 Apr 05

the formulas would be in cells f2 to f24

f2 =SUMIF(C1:C8,"=1",D1:D8)
f3 =SUMIF(C1:C8,"=2",D1:D8)
f4 =SUMIF(C1:C8,"=3",D1:D8)
F5 =SUMIF(C1:C8,"=4",D1:D8)
ETC
f24 =SUMIF(C1:C8,"=23",D1:D8)

This thread is now locked and can not be replied to.

Hands-on: Acer Predator Triton 700 review

D&AD Awards 2017: see the best design, advertising, illustration, animation and VR of the past year

How to lose weight with an Apple Watch