Text function within Sumif

[Ken Goodwin]

I would like to select the first 6 characters out of
the 'range' of a SUMIF(range, criteria, sum_range) and
check them against the criteria. Is that possible? As an
example: I tried =SUMIF(left
($C$5:$C$149,6),$D168,T$5:T$149) which does not work. It
is looking for a range of cells for the range.

Ken

 

[Arvi Laanemets]

Hi

=SUMPRODUCT(--(LEFT($C$5:$C$149,6)=$D168),T$5:T$149)


Arvi Laanemets

 

[Guest]

You can use SUMPRODUCT

=SUMPRODUCT(--(LEFT($C$5:$C$149,6)=$D$168),$T$5:$T$149)

Regards,

Peo Sjoblom

 

[Guest]

I need to search more and post less. From another post.
=SUMIF($C$5:$C$149,$D168&"*",T$5:T$149)
Works just fine.

 

[Matthew Leingang]

Hi,

Sorry for the newbie question, but from my lurks the --() operator seems to
be important and I can't figure out what it's supposed to do. Does it have
something to do with array functions?

Excel for Mac 2004 doesn't have -- in its help index anywhere I can find.
You can't google on punctuation so I'm helpless. If there's a (good) FAQ
posted somewhere please direct me.

TIA,
Matt

 

[Bernard Liengme]

An equality such as A=B has a Boolean value of TRUE or FALSE. But we need to
convert this to a numeric value (called cohesion in computer lingo). Excel
is most obliging in that when a mathematic operation is performed on a
Boolean value, it treats it as FALSE = 0 or TRUE =1. SO we could use
(A=B)*1. However the gurus have found that the double unitary negation is
more efficient (takes fewer computer cycles). You will recall that -(-2) = 2
hope this helps
(What an odd name you have -I'm allowed to say that! Are you of Swiss
descent?)

 

[Dave R.]

Here is an in depth discussion of it in addition to what's already been
said. I think there is a web page that discusses it too, but can't find it.

http://tinyurl.com/57htb

One thing to add about "--" is that it can be used to convert text numbers
from text to numbers within a formula, not just convert true/false values to
1s and 0s.

For example if you have:
BG3435
GF3564
MH5669


and want to count the number of these entries with 3 as the 3rd character,
you could use:
=SUMPRODUCT(--(--MID(A1:A3,3,1)=3))
to get the correct result of 2.
=SUMPRODUCT(--(MID(A1:A3,3,1)=3))
will return a 0 since the text functions (e.g., MID, LEFT, RIGHT) return
text and not numbers. To be comprehensive, you could change =3 to ="3" and
it would count as well. Though, if you were obtaining the 3 from the =3 part
from a calculation, then you would have to use extra functions to represent
it as text (had you not used the -- before MID), which is why using --
before MID is desirable in this instance.

Similarly, you can use -- before a range in formulas, such as:

=SUMPRODUCT(--(--A1:A100=5))

to count the number of times '5' is the value of the cells in A1:A100. If
you are dealing with data that may not (100% of the time) be formatted as
numbers, you may want to use that to convert even text values of "5" to the
number 5 to be counted with this formula.

 

[Bernard Liengme]

http://www.mcgimpsey.com/excel/formulae/doubleneg.html

 

[Matthew Leingang]

Thanks to Dave and Bernard for the help. At first I thought it had to do
with unitary decrementing, but that didn't seem to make sense. Convert
boolean to number...of course!