Mysterious Wrong Data Type Error

[tlambert]

I am using the SUMIF function to total up some vehcile types SUMIF(vehicletypes,A1,Range).

The criteria in A1 is text. I have six different vehicle types that I sum up. The formula works with four of the vehicle types just fine but with two of them it returns the "wrong data type" error.

the six vehicle types are: Minivan, Minibus, Transit Bus, Wheelchair Van, ADA Minibus, and ADA Bus. The error is returned whenever I use Transit Bus and ADA Minibus.

I can do a work around and simply change the name but I want to understand and fix the problem. Does anyone have any thoughts?

<table width="146" border="0" cellpadding="0" cellspacing="0"><col style="width: 110pt;" width="146"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt; width: 110pt;" width="146" height="17">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">
</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl65" style="height: 12.75pt;" height="17">
</td> </tr> </tbody></table>

 

[hallingh]

The only thing I can think of is make sure all of the cells in your range are set to general formatting. I did the formula in a blank workbook just to be sure and it worked fine with all of the cells set to the default general formatting.

Hank

 

[Andrew Poulsom]

What exactly is a "wrong data type" error?

 

[tlambert]

All cells are set to General. More specifically, a #VALUE! appears in the cell where my formula is. The strange thing is it only appears when "Transit Bus" or "ADA Bus" is typed into the reference cell. It works fine even if I type in "Lady Gaga".

 

[Jonmo1]

It is likely that one or more of the cells in "Range" contains the #Value! error.

Particluarly one that has Transit Bus and/or ADA Minibus in the corresponding Vehichle Types Row.

 

[tlambert]

Yes! That was it!
Works perfectly now.