SUMIF / COUNTIF Problem

[Guest]

I have a list of extensions I want to count or sum their respective sizes.
However, the SUMIF formula treats: .0 .00 .000 as 0. I need each
one to be counted or summed. I have tried to force the issue with TEXT() to
no avail.

Any assistance would be appreciated!

 

[VBA Noob]

Can you format cells with .000 to text ??

Then this would work

=SUMPRODUCT(--(I17:I19=".0"))

VBA Noob

 

[Guest]

They are already converted to text.

The problem is that I have varying extension lists. I'm trying to whittle
600,000 extension summaries down to a unique list. I can do that fine but
when I call upon Excel to sum the following:

.0 3
.0 1
.0 4
.00 2
.00 1
.00 1
.000 3
.000 1
.000 4
.000 1

If I SUMIF normally, I'll get all these counted/summed - even if I select a
cell containing just ".0".

 

[daddylonglegs]

Try using SUMPRODUCT insted of SUMIF, i.e. for a count

=SUMPRODUCT(--(A1:A10=".0"))

or for a sum

=SUMPRODUCT(--(A1:A10=".0"),B1:B10

 

[Guest]

That still fails. In fact, I tried an array formula the minute I could
but I didn't want to bog the box down. Either array formula method I use
does not see the extra decimal places (even though it's text).

It's annoying and it won't go away unless I resort to VBA. :/

 

[Guest]

Scratch that, the array formulas work. I forgot to trim out the leading
space. That's my error.

But still, why can't we force the argument to be looked up as text?

 

[VBA Noob]

Works for me

See attached

http://cjoint.com/?hxluHPI7Xj

VBA Noob

 

[Guest]

Yes, arrays work. SUMIF and COUNTIF do not though. Arrays are far slower.