How to fix formula returning "FALSE"

Sean15

Well-known Member
Joined
Jun 25, 2005
Messages
582
Office Version
2010
Platform
Windows
Hi:

In column I, for example, All values for "Alice" needs to be summed or grouped together to the first occurring "Alice". All duplicates of "Alice" need to show 0 but formula is returning "FALSE"

Could you fix formula please?


<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Customer</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;background-color: #D9E1F2;;">Oct</td><td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;background-color: #D9E1F2;;">Nov</td><td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;background-color: #D9E1F2;;">Dec</td><td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;background-color: #D9E1F2;;">Jan</td><td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;background-color: #D9E1F2;;">Feb</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: center;background-color: #D9E1F2;;">Total</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Alice</td><td style="text-align: right;;"></td><td style="text-align: right;border-top: 1px solid black;;"> - </td><td style="text-align: right;border-top: 1px solid black;;"> - </td><td style="text-align: right;border-top: 1px solid black;;"> 3,234.00 </td><td style="text-align: right;border-top: 1px solid black;;"> - </td><td style="text-align: right;border-top: 1px solid black;;"> 2,464.00 </td><td style="text-align: right;;"></td><td style="text-align: right;;"> 7,289.00 </td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Alice</td><td style="text-align: right;;"></td><td style="text-align: right;;"> - </td><td style="text-align: right;;"> - </td><td style="text-align: right;;"> 999.00 </td><td style="text-align: right;;"> - </td><td style="text-align: right;;"> 592.00 </td><td style="text-align: right;;"></td><td style="text-align: right;;"> FALSE </td><td style=";">required value 0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Tom</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;;"> 577.50 </td><td style="text-align: right;;"> 577.50 </td><td style="text-align: right;;"></td><td style="text-align: right;;"> 8,150.00 </td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Tom</td><td style="text-align: right;;"></td><td style="text-align: right;;"> - </td><td style="text-align: right;;"> 1,925.00 </td><td style="text-align: right;;"> 1,925.00 </td><td style="text-align: right;;"> 1,665.00 </td><td style="text-align: right;;"> 1,480.00 </td><td style="text-align: right;;"></td><td style="text-align: right;;"> FALSE </td><td style=";">required value 0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Ryan</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;;"> 1,925.00 </td><td style="text-align: right;;"></td><td style="text-align: right;;"> 1,925.00 </td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">Courtyard</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;;"> 740.00 </td><td style="text-align: right;;"></td><td style="text-align: right;;"> 740.00 </td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">Double</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;;"> 96.25 </td><td style="text-align: right;;"></td><td style="text-align: right;;"> 540.25 </td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">Double</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;;"> 444.00 </td><td style="text-align: right;;"></td><td style="text-align: right;;"> FALSE </td><td style=";">required value 0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">Face</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;;"> 11,550.00 </td><td style="text-align: right;;"> - </td><td style="text-align: right;;"></td><td style="text-align: right;;"> 11,550.00 </td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</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><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></tbody></table><p style="width:24em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Bytemark pass type & sales (2)</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><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: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><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: rgb(218,231,245);color: rgb(22,17,32)">I2</th><td style="text-align:left">=IFERROR(<font color="Blue">IF(<font color="Red">COUNTIF(<font color="Green">A$2:A2,A2</font>)=1,SUMPRODUCT(<font color="Green">(<font color="Purple">$A$2:$A$10=A2</font>)*(<font color="Purple">$C$2:$G$10</font>)</font>)</font>),""</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I3</th><td style="text-align:left">=IFERROR(<font color="Blue">IF(<font color="Red">COUNTIF(<font color="Green">A$2:A3,A3</font>)=1,SUMPRODUCT(<font color="Green">(<font color="Purple">$A$2:$A$10=A3</font>)*(<font color="Purple">$C$2:$G$10</font>)</font>)</font>),""</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I4</th><td style="text-align:left">=IFERROR(<font color="Blue">IF(<font color="Red">COUNTIF(<font color="Green">A$2:A4,A4</font>)=1,SUMPRODUCT(<font color="Green">(<font color="Purple">$A$2:$A$10=A4</font>)*(<font color="Purple">$C$2:$G$10</font>)</font>)</font>),""</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I5</th><td style="text-align:left">=IFERROR(<font color="Blue">IF(<font color="Red">COUNTIF(<font color="Green">A$2:A5,A5</font>)=1,SUMPRODUCT(<font color="Green">(<font color="Purple">$A$2:$A$10=A5</font>)*(<font color="Purple">$C$2:$G$10</font>)</font>)</font>),""</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I6</th><td style="text-align:left">=IFERROR(<font color="Blue">IF(<font color="Red">COUNTIF(<font color="Green">A$2:A6,A6</font>)=1,SUMPRODUCT(<font color="Green">(<font color="Purple">$A$2:$A$10=A6</font>)*(<font color="Purple">$C$2:$G$10</font>)</font>)</font>),""</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I7</th><td style="text-align:left">=IFERROR(<font color="Blue">IF(<font color="Red">COUNTIF(<font color="Green">A$2:A7,A7</font>)=1,SUMPRODUCT(<font color="Green">(<font color="Purple">$A$2:$A$10=A7</font>)*(<font color="Purple">$C$2:$G$10</font>)</font>)</font>),""</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I8</th><td style="text-align:left">=IFERROR(<font color="Blue">IF(<font color="Red">COUNTIF(<font color="Green">A$2:A8,A8</font>)=1,SUMPRODUCT(<font color="Green">(<font color="Purple">$A$2:$A$10=A8</font>)*(<font color="Purple">$C$2:$G$10</font>)</font>)</font>),""</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I9</th><td style="text-align:left">=IFERROR(<font color="Blue">IF(<font color="Red">COUNTIF(<font color="Green">A$2:A9,A9</font>)=1,SUMPRODUCT(<font color="Green">(<font color="Purple">$A$2:$A$10=A9</font>)*(<font color="Purple">$C$2:$G$10</font>)</font>)</font>),""</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I10</th><td style="text-align:left">=IFERROR(<font color="Blue">IF(<font color="Red">COUNTIF(<font color="Green">A$2:A10,A10</font>)=1,SUMPRODUCT(<font color="Green">(<font color="Purple">$A$2:$A$10=A10</font>)*(<font color="Purple">$C$2:$G$10</font>)</font>)</font>),""</font>)</td></tr></tbody></table></td></tr></table><br />


Thanks


Regards,
Sean
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,883
Office Version
365
Platform
Windows
Try
=IFERROR(IF(COUNTIF(A$2:A2,A2)=1,SUMPRODUCT(($A$2:$A$10=A2)*($C$2:$G$10)),0),"")
 

Sean15

Well-known Member
Joined
Jun 25, 2005
Messages
582
Office Version
2010
Platform
Windows
Excellent. Thank you.

Regards,

Sean
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,883
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,089,991
Messages
5,411,710
Members
403,391
Latest member
electropro

This Week's Hot Topics

Top