How to fix formula returning "FALSE"

Sean15

Well-known Member
Joined
Jun 25, 2005
Messages
580
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,502
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
580
Office Version
2010
Platform
Windows
Excellent. Thank you.

Regards,

Sean
 

Fluff

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

Forum statistics

Threads
1,089,195
Messages
5,406,738
Members
403,105
Latest member
gituncar

This Week's Hot Topics

Top