Sumif multiple conditions

[bloodybrit90]

Hi all,

I am trying to sum C2:C15, but exclude "#N/A" and blank cells. Here is what I have that works for NA.
=SUMIF(D2:D15,"<>#n/a")

I thought:

=SUM(IF(D2:D15,"<>#n/a")*(D2:D15,"")) would state not to count or blank cells but the equation does not work.

Any suggestions??

 

[jim may]

Excel Workbook
	A	B	C	D
1
2			3
3
4			1
5			2
6
7			#N/A
8
9			1
10			2
11			3
12
13			#N/A
14
15
16
17
18			12	<< Does this work for you?
Sheet1

Excel 2007

Cell Formulas
Range	Formula
C7	=NA()
C13	=NA()
C18	=SUMIF(C2:C15,"<>#N/A")

 

[bloodybrit90]

That is the formula I am currently using. I need excel to ignore the "-" which excel generates when the formula does not result in a number.

currently the cell with "-" has a formula that finds the item # and then multiplies the price from 1 vendor by the quantity that is required. If a vendor does not stock this item the price is $0, which results in "-" as the number.

Does that explanation help?

 

[jim may]

I'm guessing a cell that results in "displaying" the "-" is actually a Zero-Value (number). Th current formatting of the cell is displaying the "-", versus its real VALUE of 0 (zero).

 

[bloodybrit90]

Thanks, I didn't notice the formatting . how would i conditional format to ignore $0.00? =MIN(if($C4:$G4)"<>0")

 

[jim may]

When I use the Sum() anything, it by-passes the 0's. I'm not understanding your problem with the "-"'s (which are zero values), obviously.

 

[bloodybrit90]

As shown below when the formula generates a 0 the conditional formatting highlights it. if you delete the 0 then the conditional formatting works. (The formula will show 0 if a vendor does not stock an item). The sum if (the last row) then calculates the total cost for an order and then highlights the lowest value. I want excel not to sum a column if a zero exists, so if a vendor does not stock an item it will not show up as the cheapest place to purchase from.

<table border="0" cellpadding="0" cellspacing="0" width="624"><col style="width: 122pt;" width="163"> <col style="width: 84pt;" width="112"> <col style="width: 81pt;" width="108"> <col style="width: 88pt;" width="117"> <col style="width: 93pt;" width="124"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl71" style="height: 15pt; width: 122pt;" width="163" height="20">$948.88 </td> <td class="xl71" style="border-left: medium none; width: 84pt;" width="112">$948.00 </td> <td class="xl71" style="background: none repeat scroll 0% 0% yellow;" width="108">$940.00 </td> <td class="xl71" style="border-left: medium none; width: 88pt;" width="117">$942.00 </td> <td class="xl71" style="border-left: medium none; width: 93pt;" width="124">$944.45 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl71" style="height: 15pt; border-top: medium none;" height="20">$163.25 </td> <td class="xl71" style="background: none repeat scroll 0% 0% yellow;">$161.84 </td> <td class="xl71" style="border-top: medium none; border-left: medium none;">$164.80 </td> <td class="xl71" style="border-top: medium none; border-left: medium none;">$163.00 </td> <td class="xl71" style="border-top: medium none; border-left: medium none;">$165.24 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl71" style="height: 15pt; border-top: medium none;" height="20">$1,364.85 </td> <td class="xl71" style="border-top: medium none; border-left: medium none;">$1,410.00 </td> <td class="xl71" style="border-top: medium none; border-left: medium none;">$1,365.00 </td> <td class="xl71" style="border-top: medium none; border-left: medium none;">$1,385.00 </td> <td class="xl71" style="background: none repeat scroll 0% 0% yellow;">$0.00 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl71" style="height: 15pt; border-top: medium none;" height="20">#N/A</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">#N/A</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">#N/A</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">#N/A</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">#N/A</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl71" style="height: 15pt; border-top: medium none;" height="20">#N/A</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">#N/A</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">#N/A</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">#N/A</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">#N/A</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl71" style="height: 15pt; border-top: medium none;" height="20">#N/A</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">#N/A</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">#N/A</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">#N/A</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">#N/A</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl71" style="height: 15pt; border-top: medium none;" height="20">#N/A</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">#N/A</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">#N/A</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">#N/A</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">#N/A</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl71" style="height: 15pt; border-top: medium none;" height="20">#N/A</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">#N/A</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">#N/A</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">#N/A</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">#N/A</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl71" style="height: 15pt; border-top: medium none;" height="20">#N/A</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">#N/A</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">#N/A</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">#N/A</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">#N/A</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl71" style="height: 15pt; border-top: medium none;" height="20">#N/A</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">#N/A</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">#N/A</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">#N/A</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">#N/A</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl71" style="height: 15pt; border-top: medium none;" height="20">#N/A</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">#N/A</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">#N/A</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">#N/A</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">#N/A</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl71" style="height: 15pt; border-top: medium none;" height="20">#N/A</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">#N/A</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">#N/A</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">#N/A</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">#N/A</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl71" style="height: 15pt; border-top: medium none;" height="20">#N/A</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">#N/A</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">#N/A</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">#N/A</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">#N/A</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl71" style="height: 15pt; border-top: medium none;" height="20">#N/A</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">#N/A</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">#N/A</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">#N/A</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">#N/A</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl71" style="height: 15pt; border-top: medium none;" height="20">$2,476.98 </td> <td class="xl71" style="border-top: medium none; border-left: medium none;">$2,519.84 </td> <td class="xl71" style="border-top: medium none; border-left: medium none;">$2,469.80 </td> <td class="xl71" style="border-top: medium none; border-left: medium none;">$2,490.00 </td> <td class="xl71" style="background: none repeat scroll 0% 0% yellow;">$1,109.69 </td> </tr> </tbody></table>

 

[bloodybrit90]

To the top.

 

[jim may]

Try

=IF(COUNTIF(A2:A9,0)>0,0,SUMIF(A2:A9,"<>#N/A"))

 

[bloodybrit90]

It worked well enough. The conditional formatting still highlights the $0.00, but I can just delete it and than everything works. Thanks for all the help.