New Contributor

# Formula

I need to create a formula that will pull out a figure dependent on the month selected. Eg if Dec selected then i need the sum of the column for dec.

3 Replies

# Re: Formula

lets assume you have a table with row 1 having titles "Jan", "Feb",...,"Dec" and numbers below it.
And you have a cell, let's pretend it is AA1 where you enter the month (ideally using Data Validation of the list of those Column headings). And you want the total in cell AA2
There are a number of ways but here are 2:
a) assuming the data is Formatted as a Table called "Table1" (the default name given) then
b) assuming columns "Jan" ... "Dec" are columns B:M then something like
= SUM(INDEX(\$B:\$M, , MATCH(\$AA\$1,\$B\$1:\$M\$1,0)))

# Re: Formula

I am still struggling to get it to work, not sure if it is because I have it over two tabs. One has all the info laid out, the 2nd (where I want to put the formula) will be a journal which I want to automatically update figures when I change the month. Not sure how I can attach to show what I mean but hopefully this will help.

 Prepayments Schedule 2021-2022. Nominal Code Q103 Company Description Nominal Dec Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov TOTAL Specialist Risk Solutions. (CL1166) Excess Professional Indemnity Insurance K118 OVH £     285.37 £     285.37 £     257.75 £     285.37 £     276.16 £     285.37 £     276.16 £     285.37 £     285.37 £     276.16 £     285.37 £     276.16 £    3,360.00 Specialist Risk Solutions. (CL1166) PI - Primary Policy K118 OVH £     423.30 £     423.30 £     382.33 £     423.30 £     409.64 £     423.30 £     409.64 £     423.30 £     423.30 £     409.64 £     423.30 £     409.64 £    4,984.00 Specialist Risk Solutions. (CL1166) Cyber Package K118 OVH £     135.89 £     135.89 £     122.74 £     135.89 £     131.51 £     135.89 £     131.51 £     135.89 £     135.89 £     131.51 £     135.89 £     131.51 £    1,600.00 Specialist Risk Solutions. (CL1166) Management Liability Package K118 OVH £     137.54 £     137.54 £     124.23 £     137.54 £     133.10 £     137.54 £     133.10 £     137.54 £     137.54 £     133.10 £     137.54 £     133.10 £    1,619.41 Specialist Risk Solutions. (CL1166) Combined Liability K118 OVH £  3,453.93 £  3,453.93 £  3,119.68 £  3,453.93 £  3,342.51 £  3,453.93 £  3,342.51 £  3,453.93 £  3,453.93 £  3,342.51 £  3,453.93 £  3,342.51 £  40,667.20 Specialist Risk Solutions. (CL1166) Combined Liability Year End Adj 20-21 K118 OVH £     748.23 £     748.23 £     675.82 £     748.23 £     724.10 £     748.23 £     724.10 £     748.23 £     748.23 £     724.10 £     748.23 £     724.10 £    8,809.85 Specialist Risk Solutions. (CL1166) Property Owners K118 OVH £     881.20 £     881.20 £     795.92 £     881.20 £     852.77 £     881.20 £     852.77 £     881.20 £     881.20 £     852.77 £     881.20 £     852.77 £  10,375.40 Specialist Risk Solutions. (CL1166) Commercial Combined K118 OVH £     755.08 £     755.08 £     682.01 £     755.08 £     730.72 £     755.08 £     730.72 £     755.08 £     755.08 £     730.72 £     755.08 £     730.72 £    8,890.43 Specialist Risk Solutions. (CL1166) Contract Works K118 OVH £     802.19 £     802.19 £     724.56 £     802.19 £     776.31 £     802.19 £     776.31 £     802.19 £     802.19 £     776.31 £     802.19 £     776.31 £    9,445.16 Sub Total K118 OVH £  7,622.73 £  7,622.73 £  6,885.04 £  7,622.73 £  7,376.83 £  7,622.73 £  7,376.83 £  7,622.73 £  7,622.73 £  7,376.83 £  7,622.73 £  7,376.83 Specialist Risk Solutions. (CL1166) Vehicle Insurance E104 ENV £  1,439.22 £  1,439.22 £  1,299.94 £  1,439.22 £  1,392.79 £  1,439.22 £  1,392.79 £  1,439.22 £  1,439.22 £  1,392.79 £  1,439.22 £  1,392.79 £  16,945.60 Sub Total E104 ENV £  1,439.22 £  1,439.22 £  1,299.94 £  1,439.22 £  1,392.79 £  1,439.22 £  1,392.79 £  1,439.22 £  1,439.22 £  1,392.79 £  1,439.22 £  1,392.79 Test Valley Council (TE109) Rates K135 OVH £     861.00 £     861.00 £     861.00 £     861.00 £     861.00 £     861.00 £     861.00 £     861.00 £     861.00 £     861.00 £     861.00 £     861.00 £  10,332.00 Test Valley Council (TE109) Rates K135 OVH £     624.00 £     624.00 £     624.00 £     624.00 £     624.00 £     624.00 £     624.00 £     624.00 £     624.00 £     624.00 £     624.00 £     624.00 £    7,488.00 Sub Total K135 OVH £  1,485.00 £  1,485.00 £  1,485.00 £  1,485.00 £  1,485.00 £  1,485.00 £  1,485.00 £  1,485.00 £  1,485.00 £  1,485.00 £  1,485.00 £  1,485.00 Achilles Information Ltd Accreditation K101 OVH £     181.07 £     181.07 £     163.55 £     181.07 £     175.23 £     181.07 £     175.23 £     181.07 £     181.07 £     175.23 £     181.07 £     175.23 £    2,132.00 CHAS Accreditation K101 OVH £                 - ISOQAR Accreditation K101 OVH £     444.12 £     444.12 £     401.14 £     444.12 £     429.79 £     444.12 £     429.79 £     444.12 £     444.12 £     429.79 £     444.12 £     429.79 £    5,229.17 K101 OVH £     625.20 £     625.20 £     564.69 £     625.20 £     605.03 £     625.20 £     605.03 £     625.20 £     625.20 £     605.03 £     625.20 £     605.03 HSE (Due 27th Nov 2023) Licensing K149 OVH £        95.26 £        95.26 £        86.05 £        95.26 £        92.19 £        95.26 £        92.19 £        95.26 £        95.26 £        92.19 £        95.26 £        92.19 £    1,121.67 K149 OVH £        95.26 £        95.26 £        86.05 £        95.26 £        92.19 £        95.26 £        92.19 £        95.26 £        95.26 £        92.19 £        95.26 £        92.19 Construction Line Trade Association K139 OVH £     146.00 £     146.00 £     131.87 £     146.00 £     141.29 £     146.00 £     141.29 £     146.00 £     146.00 £     141.29 £     146.00 £     141.29 £    1,719.00 ARCA Trade Association K139 OVH £        93.42 £        93.42 £        84.38 £        93.42 £        90.41 £        93.42 £        90.41 £        93.42 £        93.42 £        90.41 £        93.42 £        90.41 £    1,100.00 K139 OVH £     239.42 £     239.42 £     216.25 £     239.42 £     231.70 £     239.42 £     231.70 £     239.42 £     239.42 £     231.70 £     239.42 £     231.70

 Journal Posting Dec K118 OVH

All help gratefully received as this is driving me mad now, because I know it should be easy but I am a bit rusty, not having had to do stuff like this for a while now.