SUMIF / COUNTIF Problem
 

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!

SUMIF / COUNTIF Problem
 

Can you format cells with .000 to text ??

Then this would work

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

VBA Noob


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=564040

SUMIF / COUNTIF Problem
 

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




"VBA Noob" wrote:


Can you format cells with .000 to text ??

Then this would work

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

VBA Noob


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=564040

SUMIF / COUNTIF Problem
 

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)


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=564040

SUMIF / COUNTIF Problem
 

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


"daddylonglegs" wrote:


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)


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=564040

SUMIF / COUNTIF Problem
 

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?

"Rif" wrote:

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


"daddylonglegs" wrote:


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)


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=564040

SUMIF / COUNTIF Problem
 

Works for me

See attached

http://cjoint.com/?hxluHPI7Xj

VBA Noob


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=564040

SUMIF / COUNTIF Problem
 

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

"VBA Noob" wrote:


Works for me

See attached

http://cjoint.com/?hxluHPI7Xj

VBA Noob


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=564040