Forum Discussion
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.
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
= SUM(INDEX(Table1[#Data], ,MATCH(AA1,Table1[#Headers])))
b) assuming columns "Jan" ... "Dec" are columns B:M then something like
= SUM(INDEX($B:$M, , MATCH($AA$1,$B$1:$M$1,0)))
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
= SUM(INDEX(Table1[#Data], ,MATCH(AA1,Table1[#Headers])))
b) assuming columns "Jan" ... "Dec" are columns B:M then something like
= SUM(INDEX($B:$M, , MATCH($AA$1,$B$1:$M$1,0)))
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.
Thanks in advance
Tracie.
Tracie_Barker1270 i attached a sheet with some examples
