Need Formula based solution

lesliewheeler

New Member
Joined
Dec 1, 2016
Messages
32
Office Version
  1. 2013
Platform
  1. Windows
I have attached the sample work sheet below have given the detailing for the desired result below the summary.
You can copy paste the below contents
Pls help me need this very urgently need a formula based solution

Sr. No. DescriptionOther DetailsDimensions (inches)Dimensions (Feet)AreaUnitsQty. Prodn CostService CostG. TOTAL (Rs.)
Article CodeWidthHeightWidthHeightTotal Sq FtRate (Rs.)Amount (Rs.)Tax %Total Amt. (Rs.)Article CodeSERVICE ELEMENTRate (Rs.)Amount (Rs.)Tax %Total Amt. (Rs.)
1720101774 32.062.02.75.213.8sq.ft.1 13.785006888.95.00% 7,233.33720102379 568.915.00% 79.22 7,312.56
2720101779 24.024.02.02.04.0sq.ft.12 48.001004800.05.00% 5,040.00720102379 5240.015.00% 276.00 5,316.00
3720101782 24.024.02.02.04.0sq.ft.6 24.0012288.05.00% 302.40720102379 5120.015.00% 138.00 440.40
4720101779 24.024.02.02.04.0sq.ft.12 48.001004800.05.00% 5,040.00720102379 5240.015.00% 276.00 5,316.00
5720101779 24.024.02.02.04.0sq.ft.12 48.001004800.05.00% 5,040.00720102379 5240.015.00% 276.00 5,316.00
6720101779 24.024.02.02.04.0sq.ft.12 48.001004800.05.00% 5,040.00720102379 5240.015.00% 276.00 5,316.00
7720101779 24.024.02.02.04.0sq.ft.12 48.001004800.05.00% 5,040.00720102379 5240.015.00% 276.00 5,316.00
8720101779 24.024.02.02.04.0sq.ft.12 48.001004800.05.00% 5,040.00720102379 5240.015.00% 276.00 5,316.00
9720101774 32.062.02.75.213.8sq.ft.1 13.785006888.95.00% 7,233.33720102379 568.915.00% 79.22 7,312.56
10720101774 32.062.02.75.213.8sq.ft.1 13.785006888.95.00% 7,233.33720102379 568.915.00% 79.22 7,312.56
Result Summary required
Example of the result
Article CodeSq.ftCost
From Column B72010177441.3321700
From Column B72010177928828800
From Column B72010178224288
From Column Q720102379353.332031.67
Removes duplicates and Return Unique Values from Column B and Column QReturns Total Sum of the article code from column LReturns Sum of all article codes, Condition:-First it checks if the article code in present in column B if yes then returns sum from cloumn N for the the article code. If the code is not in column B then it checks in cloumn Q and returns the sum in column V, else retuns blank value

<colgroup><col><col><col><col><col span="4"><col><col><col><col><col><col><col><col><col><col><col><col><col span="2"><col></colgroup><tbody>
</tbody>
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

lesliewheeler

New Member
Joined
Dec 1, 2016
Messages
32
Office Version
  1. 2013
Platform
  1. Windows
Re: Urgent- Need Help- Need Formula based solution

Pls help me someone....need solution desparately!!!!
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,243
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Re: Urgent- Need Help- Need Formula based solution

try this

<b>Excel 2012</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">14</td><td style=";">Result Summary required</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style=";">Example of the result</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;"></td><td style=";">Article Code</td><td style=";">Sq.ft</td><td style=";">Cost</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style=";">From Column B</td><td style="text-align: right;background-color: #E2EFDA;;">720101774</td><td style="text-align: right;background-color: #E2EFDA;;">41.34</td><td style="text-align: right;background-color: #E2EFDA;;">20666.7</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style=";">From Column B</td><td style="text-align: right;background-color: #E2EFDA;;">720101779</td><td style="text-align: right;background-color: #E2EFDA;;">288</td><td style="text-align: right;background-color: #E2EFDA;;">28800</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style=";">From Column B</td><td style="text-align: right;background-color: #E2EFDA;;">720101782</td><td style="text-align: right;background-color: #E2EFDA;;">24</td><td style="text-align: right;background-color: #E2EFDA;;">288</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;;"></td><td style="background-color: #E2EFDA;;"></td><td style="text-align: right;background-color: #E2EFDA;;">0</td><td style="text-align: right;background-color: #E2EFDA;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style=";">From Column Q</td><td style="text-align: right;background-color: #FFF2CC;;">720102379</td><td style="text-align: right;background-color: #FFF2CC;;">353.34</td><td style="text-align: right;background-color: #FFF2CC;;">2031.66</td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: right;;"></td><td style="background-color: #FFF2CC;;"></td><td style="text-align: right;background-color: #FFF2CC;;">0</td><td style="text-align: right;background-color: #FFF2CC;;">0</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C17</th><td style="text-align:left">=IF(<font color="Blue">ISERROR(<font color="Red">MATCH(<font color="Green">B17,$B$3:$B$12,0</font>)</font>),SUMIF(<font color="Red">$Q$3:$Q$12,B17,$L$3:$L$12</font>),SUMIF(<font color="Red">$B$3:$B$12,B17,$L$3:$L$12</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D17</th><td style="text-align:left">=IF(<font color="Blue">ISERROR(<font color="Red">MATCH(<font color="Green">B17,$B$3:$B$12,0</font>)</font>),SUMIF(<font color="Red">$Q$3:$Q$12,B17,$V$3:$V$12</font>),SUMIF(<font color="Red">$B$3:$B$12,B17,$N$3:$N$12</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C21</th><td style="text-align:left">=IF(<font color="Blue">ISERROR(<font color="Red">MATCH(<font color="Green">B21,$B$3:$B$12,0</font>)</font>),SUMIF(<font color="Red">$Q$3:$Q$12,B21,$L$3:$L$12</font>),SUMIF(<font color="Red">$B$3:$B$12,B21,$L$3:$L$12</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D21</th><td style="text-align:left">=IF(<font color="Blue">ISERROR(<font color="Red">MATCH(<font color="Green">B21,$B$3:$B$12,0</font>)</font>),SUMIF(<font color="Red">$Q$3:$Q$12,B21,$V$3:$V$12</font>),SUMIF(<font color="Red">$B$3:$B$12,B21,$N$3:$N$12</font>)</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B17</th><td style="text-align:left">{=IFERROR(<font color="Blue">INDEX(<font color="Red">$B$3:$B$12, MATCH(<font color="Green">0, COUNTIF(<font color="Purple">$B$16:B16, $B$3:$B$12</font>), 0</font>)</font>),""</font>)}</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B21</th><td style="text-align:left">{=IFERROR(<font color="Blue">INDEX(<font color="Red">$Q$3:$Q$12, MATCH(<font color="Green">0, COUNTIF(<font color="Purple">$B$20:B20, $Q$3:$Q$12</font>), 0</font>)</font>),""</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

lesliewheeler

New Member
Joined
Dec 1, 2016
Messages
32
Office Version
  1. 2013
Platform
  1. Windows
Re: Urgent- Need Help- Need Formula based solution

try this

Excel 2012
ABCD
14Result Summary required
15Example of the result
16Article CodeSq.ftCost
17From Column B72010177441.3420666.7
18From Column B72010177928828800
19From Column B72010178224288
2000
21From Column Q720102379353.342031.66
2200

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
C17=IF(ISERROR(MATCH(B17,$B$3:$B$12,0)),SUMIF($Q$3:$Q$12,B17,$L$3:$L$12),SUMIF($B$3:$B$12,B17,$L$3:$L$12))
D17=IF(ISERROR(MATCH(B17,$B$3:$B$12,0)),SUMIF($Q$3:$Q$12,B17,$V$3:$V$12),SUMIF($B$3:$B$12,B17,$N$3:$N$12))
C21=IF(ISERROR(MATCH(B21,$B$3:$B$12,0)),SUMIF($Q$3:$Q$12,B21,$L$3:$L$12),SUMIF($B$3:$B$12,B21,$L$3:$L$12))
D21=IF(ISERROR(MATCH(B21,$B$3:$B$12,0)),SUMIF($Q$3:$Q$12,B21,$V$3:$V$12),SUMIF($B$3:$B$12,B21,$N$3:$N$12))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
B17{=IFERROR(INDEX($B$3:$B$12, MATCH(0, COUNTIF($B$16:B16, $B$3:$B$12), 0)),"")}
B21{=IFERROR(INDEX($Q$3:$Q$12, MATCH(0, COUNTIF($B$20:B20, $Q$3:$Q$12), 0)),"")}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>
HI
Thank you so much for the response this is working in a way
However I would need 1 single formula in a cell that determines unique values from 2 columns and return the values. Here I have to paste 2 formulas.
(Basically the formula should first check unique value from column and return it when no further values are there is returns values from column Q)
This has to be done with 1 formula which I can copy paste down.

Thanks for your help and support.
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,243
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows

ADVERTISEMENT

Re: Urgent- Need Help- Need Formula based solution

ok, try this

<b>Excel 2012</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">15</td><td style=";">Example of the result</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;"></td><td style=";">Article Code</td><td style=";">Sq.ft</td><td style=";">Cost</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style=";">From Column B</td><td style="text-align: right;;">720101774</td><td style="text-align: right;background-color: #E2EFDA;;">41.34</td><td style="text-align: right;background-color: #E2EFDA;;">20666.7</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style=";">From Column B</td><td style="text-align: right;;">720101779</td><td style="text-align: right;background-color: #E2EFDA;;">288</td><td style="text-align: right;background-color: #E2EFDA;;">28800</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style=";">From Column B</td><td style="text-align: right;;">720101782</td><td style="text-align: right;background-color: #E2EFDA;;">24</td><td style="text-align: right;background-color: #E2EFDA;;">288</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;;"></td><td style="text-align: right;;">720102379</td><td style="text-align: right;background-color: #E2EFDA;;">353.34</td><td style="text-align: right;background-color: #E2EFDA;;">2031.66</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C17</th><td style="text-align:left">=IF(<font color="Blue">ISERROR(<font color="Red">MATCH(<font color="Green">B17,$B$3:$B$12,0</font>)</font>),SUMIF(<font color="Red">$Q$3:$Q$12,B17,$L$3:$L$12</font>),SUMIF(<font color="Red">$B$3:$B$12,B17,$L$3:$L$12</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D17</th><td style="text-align:left">=IF(<font color="Blue">ISERROR(<font color="Red">MATCH(<font color="Green">B17,$B$3:$B$12,0</font>)</font>),SUMIF(<font color="Red">$Q$3:$Q$12,B17,$V$3:$V$12</font>),SUMIF(<font color="Red">$B$3:$B$12,B17,$N$3:$N$12</font>)</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B17</th><td style="text-align:left">{=IFERROR(<font color="Blue">IFERROR(<font color="Red">INDEX(<font color="Green">$B$3:$B$12, MATCH(<font color="Purple">0, COUNTIF(<font color="Teal">$B$16:B16, $B$3:$B$12</font>), 0</font>)</font>), INDEX(<font color="Green">$Q$3:$Q$12, MATCH(<font color="Purple">0, COUNTIF(<font color="Teal">$B$16:B16, $Q$3:$Q$12</font>), 0</font>)</font>)</font>), ""</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

lesliewheeler

New Member
Joined
Dec 1, 2016
Messages
32
Office Version
  1. 2013
Platform
  1. Windows
Re: Urgent- Need Help- Need Formula based solution

ok, try this

Excel 2012
ABCD
15Example of the result
16Article CodeSq.ftCost
17From Column B72010177441.3420666.7
18From Column B72010177928828800
19From Column B72010178224288
20720102379353.342031.66

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
C17=IF(ISERROR(MATCH(B17,$B$3:$B$12,0)),SUMIF($Q$3:$Q$12,B17,$L$3:$L$12),SUMIF($B$3:$B$12,B17,$L$3:$L$12))
D17=IF(ISERROR(MATCH(B17,$B$3:$B$12,0)),SUMIF($Q$3:$Q$12,B17,$V$3:$V$12),SUMIF($B$3:$B$12,B17,$N$3:$N$12))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
B17{=IFERROR(IFERROR(INDEX($B$3:$B$12, MATCH(0, COUNTIF($B$16:B16, $B$3:$B$12), 0)), INDEX($Q$3:$Q$12, MATCH(0, COUNTIF($B$16:B16, $Q$3:$Q$12), 0))), "")}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>


Hey Thanks,
Just one bit of help required.
When I am copying the formula down after B17 then after all unique values in column B which has approx 50 rows (currently we are using only 10 rows) it return 0 and then the column q values.
Is there any way I can get serial wise information without the 0
Example is below

Article Code
720101774
720101779
720101782
0
720102379

<colgroup><col></colgroup><tbody>
</tbody>
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,243
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows

ADVERTISEMENT

Re: Urgent- Need Help- Need Formula based solution

one way to rid of the 0 is to match the exact no of rows to the ranges $B$3:$B$12 & $Q$3:$Q$12.

e.g. for 100 rows, the ranges should be $B$3:$B$102 & $Q$3:$Q$102 etc
 

lesliewheeler

New Member
Joined
Dec 1, 2016
Messages
32
Office Version
  1. 2013
Platform
  1. Windows
Re: Urgent- Need Help- Need Formula based solution

I have done the same mapped the required ranges incase any data is filled and in doing so this gives the 0 value.
If we are keeping only till value entered it works like a charm but in a given range if blank cells it returns 0.
Can you sort of make addition in the formula that if it encounters blank cell in range B then it automatically start reference from range Q .

Thank you so much for your patience and support.
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,243
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Re: Urgent- Need Help- Need Formula based solution

this is another work around, fill the empty cells with *.

before

<b>Excel 2012</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Sr. No.</td><td style="text-align: right;;"></td><td style=";">Description</td><td style=";">Other Details</td><td style=";">Dimensions (inches)</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Article Code</td><td style=";">Width</td><td style=";">Height</td><td style=";">Width</td><td style=";">Height</td><td style=";">Total Sq Ft</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">1</td><td style="text-align: right;;">720101774</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">32</td><td style="text-align: right;;">62</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">2</td><td style="text-align: right;;">720101779</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">24</td><td style="text-align: right;;">24</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">720101782</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">24</td><td style="text-align: right;;">24</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">4</td><td style="text-align: right;;">720101779</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">24</td><td style="text-align: right;;">24</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">5</td><td style="text-align: right;;">720101779</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">24</td><td style="text-align: right;;">24</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">24</td><td style="text-align: right;;">24</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">24</td><td style="text-align: right;;">24</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">24</td><td style="text-align: right;;">24</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">32</td><td style="text-align: right;;">62</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">32</td><td style="text-align: right;;">62</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style=";">Result Summary required</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style=";">Example of the result</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;"></td><td style=";">Article Code</td><td style=";">Sq.ft</td><td style=";">Cost</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;"></td><td style="text-align: right;;">720101774</td><td style="text-align: right;background-color: #E2EFDA;;">13.78</td><td style="text-align: right;background-color: #E2EFDA;;">6888.9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;"></td><td style="text-align: right;;">720101779</td><td style="text-align: right;background-color: #E2EFDA;;">144</td><td style="text-align: right;background-color: #E2EFDA;;">14400</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;"></td><td style="text-align: right;;">720101782</td><td style="text-align: right;background-color: #E2EFDA;;">24</td><td style="text-align: right;background-color: #E2EFDA;;">288</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;background-color: #E2EFDA;;">0</td><td style="text-align: right;background-color: #E2EFDA;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style=";">From Column Q</td><td style="text-align: right;;">720102379</td><td style="text-align: right;background-color: #E2EFDA;;">353.34</td><td style="text-align: right;background-color: #E2EFDA;;">2031.66</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br />

after

<b>Excel 2012</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Sr. No.</td><td style="text-align: right;;"></td><td style=";">Description</td><td style=";">Other Details</td><td style=";">Dimensions (inches)</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Article Code</td><td style=";">Width</td><td style=";">Height</td><td style=";">Width</td><td style=";">Height</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">1</td><td style="text-align: right;;">720101774</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">32</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">2</td><td style="text-align: right;;">720101779</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">24</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">720101782</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">24</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">4</td><td style="text-align: right;;">720101779</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">24</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">5</td><td style="text-align: right;;">720101779</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">24</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">6</td><td style="text-align: center;;">*</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">24</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">7</td><td style="text-align: center;;">*</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">24</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">8</td><td style="text-align: center;;">*</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">24</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">9</td><td style="text-align: center;;">*</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">32</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">10</td><td style="text-align: center;;">*</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">32</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style=";">Result Summary required</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style=";">Example of the result</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;"></td><td style=";">Article Code</td><td style=";">Sq.ft</td><td style=";">Cost</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;"></td><td style="text-align: right;;">720101774</td><td style="text-align: right;background-color: #E2EFDA;;">13.78</td><td style="text-align: right;background-color: #E2EFDA;;">6888.9</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;"></td><td style="text-align: right;;">720101779</td><td style="text-align: right;background-color: #E2EFDA;;">144</td><td style="text-align: right;background-color: #E2EFDA;;">14400</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;"></td><td style="text-align: right;;">720101782</td><td style="text-align: right;background-color: #E2EFDA;;">24</td><td style="text-align: right;background-color: #E2EFDA;;">288</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;;"></td><td style="text-align: right;;">720102379</td><td style="text-align: right;background-color: #E2EFDA;;">353.34</td><td style="text-align: right;background-color: #E2EFDA;;">2031.66</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style=";">From Column Q</td><td style=";"></td><td style="text-align: right;background-color: #E2EFDA;;">0</td><td style="text-align: right;background-color: #E2EFDA;;">0</td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C17</th><td style="text-align:left">=IF(<font color="Blue">ISERROR(<font color="Red">MATCH(<font color="Green">B17,Sheet1!$B$3:$B$12,0</font>)</font>),SUMIF(<font color="Red">Sheet1!$Q$3:$Q$12,B17,Sheet1!$L$3:$L$12</font>),SUMIF(<font color="Red">Sheet1!$B$3:$B$12,B17,Sheet1!$L$3:$L$12</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D17</th><td style="text-align:left">=IF(<font color="Blue">ISERROR(<font color="Red">MATCH(<font color="Green">B17,Sheet1!$B$3:$B$12,0</font>)</font>),SUMIF(<font color="Red">Sheet1!$Q$3:$Q$12,B17,Sheet1!$V$3:$V$12</font>),SUMIF(<font color="Red">Sheet1!$B$3:$B$12,B17,Sheet1!$N$3:$N$12</font>)</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B17</th><td style="text-align:left">{=IFERROR(<font color="Blue">IFERROR(<font color="Red">INDEX(<font color="Green">Sheet1!$B$3:$B$8, MATCH(<font color="Purple">0, COUNTIF(<font color="Teal">$B$16:B16, Sheet1!$B$3:$B$8</font>), 0</font>)</font>), INDEX(<font color="Green">Sheet1!$Q$3:$Q$8, MATCH(<font color="Purple">0, COUNTIF(<font color="Teal">$B$16:B16, Sheet1!$Q$3:$Q$8</font>), 0</font>)</font>)</font>), ""</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

lesliewheeler

New Member
Joined
Dec 1, 2016
Messages
32
Office Version
  1. 2013
Platform
  1. Windows
Re: Urgent- Need Help- Need Formula based solution

OMG it works like a charm... I am so obliged to you..I love you man..

Thank you so much dear friend.

I am short of words to thank you enough.

Thanks for your patience and appreciate the time you have dedicated towards for my queries
 

Watch MrExcel Video

Forum statistics

Threads
1,130,309
Messages
5,641,447
Members
417,209
Latest member
Agbarker

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
Top