SUMIF / COUNTIF Problem

R

Rif

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!
 
V

VBA Noob

Can you format cells with .000 to text ??

Then this would work

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

VBA Noob
 
R

Rif

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".
 
D

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
 
R

Rif

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. :/
 
R

Rif

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?
 
Top