How to fix formula returning "FALSE"

Sean15

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

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,943
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
585
Office Version
2010
Platform
Windows
Excellent. Thank you.

Regards,

Sean
 

Fluff

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

Watch MrExcel Video

Forum statistics

Threads
1,090,103
Messages
5,412,444
Members
403,427
Latest member
mese0115

This Week's Hot Topics

Top