How to fix formula returning "FALSE"

Sean15

Well-known Member
Joined
Jun 25, 2005
Messages
573
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,573
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),"")
 

Fluff

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

Forum statistics

Threads
1,085,193
Messages
5,382,242
Members
401,781
Latest member
Dlloyd15

Some videos you may like

This Week's Hot Topics

Top