Extract values from different columns

KPN

New Member
Joined
Sep 11, 2010
Messages
4
I would like to extract the values from the column Total due in the different ranges.
I have used an HLOOKUP formula, but because the ranges are not in alignment I get inconsistent values from my formula.
How can this be solved?
Thanks.
JUNIOR Mark ^0 Anthony 2015.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXY
1Register No.HOUSEGenderBOARDER/ DAYB/FFEESAdmission formCaution moneyMilkHair CutSwimmingUniform Tour Medicine Holiday package Total Due1st Instal. Particulars2nd Instal.Particulars3rd Instal.ParticularsBALANCETotal Due
2ST.ANTHONY0
3JUNIOR ONE0
4J1664AMPEIRE MERCYFB20,0001,00020,00030,00071,00041,000rct680910,000rct682120,000rct6881071000
5J1666KEMIGISHA ARIANNAHFD150,00030,00020,0001,00010,000211,00030,000rct684897,500rct692383,500rct7027/70850211000
6 ST. MARK0
7J1616ARINDA MONICAFB20,0001,00020,00030,00010,00021,0005,000107,00068,300rct7022/702310,000rct721428,700107000
8J1651ATWIINE NGABOYAITUMB450,00020,0001,00020,00050,00010,00038,3005,000594,300299,800rct686430,000rct7098264,500rct7255/72560594300
9J1662ISHIMWE KAMALI EGIDIAFB450,00030,00020,00020,0001,00020,00085,00010,000636,00030,000rct1321381,000rct1375/1376225,000rct6819/72500636000
10J1667AKAMPURIRA SHANITAHFD250,00030,00020,00020,0001,00020,000341,00030,000rct1327150,000rct1336161,000rct73090341000
11TOTAL01,300,00090,00060,000100,0006,000100,000205,00030,00059,30010,0001,960,300499,1000678,5000754,000028,7001960300
12 FEES STATUS FOR LENT (1ST) TERM 2015 0
13Register No.HOUSEGenderBOARDER DAYB/FFEESMilkHair CutSwimmingMedicineUniformHoliday package Tour Total Due1st Instal. Particulars2nd Instal.Particulars3rd Instal.ParticularsBALANCEParticulars
14ST. ANTHONY0
15JUNIOR FOUR0
16J1217MUGORE WASE RABIAFB225,00020,0001,00020,00025,1505,00010,000306,150250,000rct137230,000rct680626,150rct72330rct1372
17J1531MPABWANAYO NESTOLMB450,00020,0001,00020,00013,6005,00010,000519,600519,600rct68950rct6895
18J1607UWASE QUEENFB450,00020,0001,00020,00023,5005,00010,000529,500300,000rct6920229,500rct71540rct6920
19J1587GWIZA OLIGA FB450,00020,0001,00020,0008,50085,0005,00010,000599,500342,300rct6938/6941257,200rct73200rct6938/6941
20J1306NYANGOMA RHINAHFB20,0001,00020,0005,3005,00010,00061,30061,300rct68080rct6808
21J1599UWITONZE RATIFAFB450,00020,0001,00020,00014,75010,0005,00010,000530,750280,000rct7100/7203250,750rct73010rct7100/7203
22J1656ARINAITWE PATIENCEFD59,20030,00020,0001,0002,5005,00010,000127,70059,200rct131468,500rct72120rct1314
23ST.MARK0
24J1566MUGISHA RIGHANMB450,00020,0001,00020,0002,5005,00010,000508,500200,000rct1382275,000rct732633,500rct1382
25J1551HIRWA MABASI KETIAFB120,000450,00020,0001,00020,00013,05010,0005,00010,000649,050160,000rct1383270,000rct7135150,000rct7138/733369,050rct1383
26J1225ALLY FILLS MUSONIMB450,00020,0001,00020,00030,3005,00010,000536,300536,300rct13910rct1391
27J1473NAKAFERO QUEENFB49,40015,0001,00029,5005,00010,000109,90025,000rct717284,900rct73400rct7172
28J1553LOMIRO MARKMB5,8005,00010,00020,80020,8000
29J1651ISHIMWE JOELMB450,00020,0001,00020,0005,00010,000506,000506,000rct13430rct1343
30J1634MANZI MAXIMMB450,00020,0001,00020,0005,00010,000506,000225,000rct1397281,000rct71310rct1397
31TOTAL228,6004,320,000240,00013,000220,000174,450105,00070,000140,0005,511,0503,464,70001,746,8500176,1500123,3500
Lent 2015
Cell Formulas
RangeFormula
P4:P5,P7:P10P4=SUM(E4:O4)
W4:W5,U24:U30,U16:U22,W7:W10W4=P4-Q4-S4-U4
E11:W11E11=SUM(E3:E10)
N16:N22,N24:N30N16=SUM(E16:M16)
E31:U31E31=SUM(E16:E30)
Y1:Y31Y1=HLOOKUP($Z$1,$A$1:$W$67,ROW(X1),FALSE)
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Hi KPN,

I couldn't get this one without the helper column, maybe you could use this;

Book1
ABNOPXYZ
1Register No.HOUSEMedicine Holiday package Total Due16
2ST.ANTHONY16$0.00
3JUNIOR ONE16$0.00
4J1664AMPEIRE MERCY7100016$71,000.00
5J1666KEMIGISHA ARIANNAH21100016$211,000.00
6 ST. MARK16$0.00
7J1616ARINDA MONICA21000500010700016$107,000.00
8J1651ATWIINE NGABOYAITU38300500059430016$594,300.00
9J1662ISHIMWE KAMALI EGIDIA63600016$636,000.00
10J1667AKAMPURIRA SHANITAH34100016$341,000.00
11TOTAL5930010000196030016$1,960,300.00
12 FEES STATUS FOR LENT (1ST) TERM 2015 16$0.00
13Register No.HOUSETotal Due1st Instal. Particulars14Total Due
14ST. ANTHONY14$0.00
15JUNIOR FOUR14$0.00
16J1217MUGORE WASE RABIA306150250000rct137214$306,150.00
17J1531MPABWANAYO NESTOL519600519600rct689514$519,600.00
18J1607UWASE QUEEN529500300000rct692014$529,500.00
19J1587GWIZA OLIGA 599500342300rct6938/694114$599,500.00
20J1306NYANGOMA RHINAH6130061300rct680814$61,300.00
21J1599UWITONZE RATIFA530750280000rct7100/720314$530,750.00
22J1656ARINAITWE PATIENCE12770059200rct131414$127,700.00
23ST.MARK14$0.00
24J1566MUGISHA RIGHAN508500200000rct138214$508,500.00
25J1551HIRWA MABASI KETIA649050160000rct138314$649,050.00
26J1225ALLY FILLS MUSONI536300536300rct139114$536,300.00
27J1473NAKAFERO QUEEN10990025000rct717214$109,900.00
28J1553LOMIRO MARK2080014$20,800.00
29J1651ISHIMWE JOEL506000506000rct134314$506,000.00
30J1634MANZI MAXIM506000225000rct139714$506,000.00
31TOTAL55110503464700014$5,511,050.00
Sheet1
Cell Formulas
RangeFormula
Y1Y1=IF(A1="Register No.",SUMPRODUCT(--(A1:W1="Total Due")*(COLUMN(A1:W1)-COLUMN(A1)+1)),0)
Y2Y2=IF(A2="Register No.",SUMPRODUCT(--(A2:W2="Total Due")*(COLUMN(A2:W2)-COLUMN(A2)+1)),Y1)
Z2Z2=INDEX(A2:W2,,Y2)
P7:P10,P4:P5P4=SUM(E4:O4)
N11:P11N11=SUM(N3:N10)
N24:N30,N16:N22N16=SUM(E16:M16)
N31:P31N31=SUM(N16:N30)
 
Upvote 0
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

See if this would suffice. (I have hidden some columns to keep the mini-sheet a bit smaller)

21 08 14.xlsm
FGMNOPQVWXY
1FEESAdmission form Tour Medicine Holiday package Total Due1st Instal. ParticularsBALANCETotal Due - 11
20
30
47100041000rct6881071000
51500003000021100030000rct7027/70850211000
60
7100002100050001070006830028700107000
845000010000383005000594300299800rct7255/72560594300
9450000300001000063600030000rct6819/72500636000
102500003000034100030000rct73090341000
1113000009000030000593001000019603004991000287001960300
120
13FEESMilk Tour Total Due1st Instal. Particulars2nd Instal.Total Due - 9
140
150
162250002000010000306150250000rct137230000306150
174500002000010000519600519600rct6895519600
184500002000010000529500300000rct6920229500529500
194500002000010000599500342300rct6938/6941257200599500
2020000100006130061300rct680861300
214500002000010000530750280000rct7100/7203250750530750
2230000200001000012770059200rct131468500127700
230
244500002000010000508500200000rct1382275000508500
254500002000010000649050160000rct1383270000649050
264500002000010000536300536300rct1391536300
27150001000010990025000rct717284900109900
28100002080020800
294500002000010000506000506000rct1343506000
304500002000010000506000225000rct1397281000506000
31432000024000014000055110503464700017468505511050
Total
Cell Formulas
RangeFormula
P4:P5,P7:P10P4=SUM(E4:O4)
W4:W5,W7:W10W4=P4-Q4-S4-U4
F11:G11,M11:Q11,V11:W11F11=SUM(F3:F10)
N16:N22,N24:N30N16=SUM(E16:M16)
F31:G31,M31:Q31F31=SUM(F16:F30)
Y1Y1="Total Due - "&MATCH("Total Due",F1:W1,0)
Y2:Y31Y2=IFNA("Total Due - "&MATCH("Total Due",F2:W2,0),INDEX(F2:W2,SUBSTITUTE(LOOKUP("Z",Y$1:Y1),"Total Due - ","")))
 
Upvote 0
Solution
You're welcome. Thanks for the follow-up. :)

It would still be useful if you took up my previous suggestion.
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
For example, if you were using Microsoft 365 you could also consider this idea.

21 08 14.xlsm
FGMNOPQVWXY
1FEESAdmission form Tour Medicine Holiday package Total Due1st Instal. ParticularsBALANCETotal Due
20
30
47100041000rct6881071000
51500003000021100030000rct7027/70850211000
60
7100002100050001070006830028700107000
845000010000383005000594300299800rct7255/72560594300
9450000300001000063600030000rct6819/72500636000
102500003000034100030000rct73090341000
1113000009000030000593001000019603004991000287001960300
120
13FEESMilk Tour Total Due1st Instal. Particulars2nd Instal.Total Due
140
150
162250002000010000306150250000rct137230000306150
174500002000010000519600519600rct6895519600
184500002000010000529500300000rct6920229500529500
194500002000010000599500342300rct6938/6941257200599500
2020000100006130061300rct680861300
214500002000010000530750280000rct7100/7203250750530750
2230000200001000012770059200rct131468500127700
230
244500002000010000508500200000rct1382275000508500
254500002000010000649050160000rct1383270000649050
264500002000010000536300536300rct1391536300
27150001000010990025000rct717284900109900
28100002080020800
294500002000010000506000506000rct1343506000
304500002000010000506000225000rct1397281000506000
31432000024000014000055110503464700017468505511050
Total (2)
Cell Formulas
RangeFormula
P4:P5,P7:P10P4=SUM(E4:O4)
W4:W5,W7:W10W4=P4-Q4-S4-U4
F11:G11,M11:Q11,V11:W11F11=SUM(F3:F10)
N16:N22,N24:N30N16=SUM(E16:M16)
F31:G31,M31:Q31F31=SUM(F16:F30)
Y2:Y31Y2=LET(td,"Total Due",y,Y$1:Y1,IF(ISTEXT(F2),td,FILTER(F2:W2,INDEX(FILTER(F$1:W1,ISTEXT(y)),COUNTIF(y,td),0)=td)))
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,337
Members
448,568
Latest member
Honeymonster123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top