SUM of multiple MIN ranges while ignoring cells with Text "N/A"(without quotes)

[b17g]

Hello,

So this is my current forumla:

=SUM(MIN(B2:N2),(B3:N3),(B4:N4),(B5:N5),(B6:N6),(B7:N7),(B8:N8),(B9:N9),(B10:N10),(B11:N11),(B12:N12))

I'm basically comparing prices for something, and certain companies don't make certain products; So I have N/A's in those boxes. How can I adjust my formula so that it ignores cells with "N/A"(without quotes)?

Thank you for your help.

 

[markmzz]

Try this of each min (array formula - use Ctrl+Shift+Enter and not only Enter):

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;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><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;background-color: #FFFF00;;">Result</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;background-color: #FFFF00;;">2</td><td style="text-align: center;;">7</td><td style="text-align: center;;">4</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">3</td><td style="text-align: center;;">8</td><td style="text-align: center;;">2</td><td style="text-align: center;;">2</td><td style="text-align: center;;">6</td><td style="text-align: center;;">9</td><td style="text-align: center;;">4</td><td style="text-align: center;;">5</td><td style="text-align: center;;">9</td><td style="text-align: center;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td></tr></tbody></table><p style="width:4.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet10</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: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;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: #E0E0F0;color: #161120">D2</th><td style="text-align:left">=NA(<font color="Blue"></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: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;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: #E0E0F0;color: #161120">A2</th><td style="text-align:left">{=MIN(<font color="Blue">IF(<font color="Red">ISERROR(<font color="Green">B2:N2</font>),1E+300,B2:N2</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 />
Markmzz

 

[b17g]

I'm not seeing a result with Ctrl+Shift+Enter. Would this be because I'm on Word 2003?

 

[b17g]

Also, what does the 1E+300 represent? Thank you again for the help.

 

[markmzz]

Also, what does the 1E+300 represent? Thank you again for the help.

A big number.

Did you see my last formula?

Markmzz

 

[b17g]

Thank a lot. I got kind of lazy with the Syntax and SUM since I'm not too proficient with excel. Here's what I came up with in the end:

=MIN(IF(ISERROR(B2:N2),1E+300,B2:N2))+MIN(IF(ISERROR(B3:N3),1E+300,B3:N3))...etc

 

[markmzz]

Thank a lot. I got kind of lazy with the Syntax and SUM since I'm not too proficient with excel. Here's what I came up with in the end:

=MIN(IF(ISERROR(B2:N2),1E+300,B2:N2))+MIN(IF(ISERROR(B3:N3),1E+300,B3:N3))...etc

It work?

Markmzz

 

[markmzz]

A suggestion, try this:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;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><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th><th>Q</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">Helper Column</td><td style="text-align: center;background-color: #FFFF00;;">Sum</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: center;;">7</td><td style="text-align: center;;">4</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">3</td><td style="text-align: center;;">8</td><td style="text-align: center;;">2</td><td style="text-align: center;;">2</td><td style="text-align: center;;">6</td><td style="text-align: center;;">9</td><td style="text-align: center;;">4</td><td style="text-align: center;;">5</td><td style="text-align: center;;">9</td><td style="text-align: center;;">3</td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">2</td><td style="text-align: center;background-color: #FFFF00;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: center;;">1</td><td style="text-align: center;;">9</td><td style="text-align: center;;">3</td><td style="text-align: center;;">9</td><td style="text-align: center;;">2</td><td style="text-align: center;;">9</td><td style="text-align: center;;">4</td><td style="text-align: center;;">7</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">8</td><td style="text-align: center;;">4</td><td style="text-align: center;;">3</td><td style="text-align: center;;">8</td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">1</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</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><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><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td></tr></tbody></table><p style="width:4.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet10</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: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;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: #E0E0F0;color: #161120">D2</th><td style="text-align:left">=NA(<font color="Blue"></font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">J3</th><td style="text-align:left">=NA(<font color="Blue"></font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">Q2</th><td style="text-align:left">=SUM(<font color="Blue">P2:P3</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: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;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: #E0E0F0;color: #161120">P2</th><td style="text-align:left">{=MIN(<font color="Blue">IF(<font color="Red">ISERROR(<font color="Green">B2:N2</font>),1E+300,B2:N2</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 />
Markmzz

 

[b17g]

Yeah it worked, but now it's not for some reason -_-. Ugh. It's assuming E1+300 is cell E1 and it's bugging out. It screwed up when I cut and pasted it from one cell to another that I had formatted with color. Here's the current formula:

=MIN(IF(ISERROR(B2:N2),E1+300,(B2:N2))+MIN(IF(ISERROR(B3:N3),E1+300,(B3:N3))+MIN(IF(ISERROR(B4:N4),E1+300,(B4:N4))+MIN(IF(ISERROR(B5:N5),E1+300,(B5:N5))+MIN(IF(ISERROR(B6:N6),E1+300,(B6:N6))+MIN(IF(ISERROR(B7:N7),E1+300,(B7:N7))+MIN(IF(ISERROR(B8:N8),E1+300,(B8:N8))+MIN(IF(ISERROR(B9:N9),E1+300,(B9:N9))+MIN(IF(ISERROR(B10:N10),E1+300,(B10:N10))+MIN(IF(ISERROR(B11:N11),E1+300,(B11:N11))+MIN(IF(ISERROR(B12:N12),E1+300,(B12,N12))

Hmmmmm! Why does it look uniform from B3:N3 and then it screws up at B9 and B10. Hopefully someone can find my error. It's having a error with The min is error for B8:N8

 

[b17g]

<a target='_blank' title='ImageShack - Image And Video Hosting' href='http://imageshack.us/photo/my-images/571/conditionsummationmin.png/'><img src='http://img571.imageshack.us/img571/9356/conditionsummationmin.png' border='0'/></a>

Uploaded with <a target='_blank' href='http://imageshack.us'>ImageShack.us</a>