count and if functions

[Kristen]

Specifically, I have two columns..One is filled with
numerical data either "100", "200", "300", or "400." The
Second column has a string of one letter, either "X"
or "U". I want to sum up the total count of "people" that
meet both criteria. For example, the count of all the
cells containing "100" in the first column AND "X" in the
second column. So far I have this function: "=COUNT(IF
($D$2:$D$135=100,IF(CODE(E$2:E$135)=88,1)))". At
appearance, the function result is Zero (which I know
should not be true), but if I click the function sign in
the tool bar, the result shows to be 47. Is it my function
thats the problem or a glitch in my program? Please, any
help would be welcomed!

 

[J.E. McGimpsey]

One way:

=SUMPRODUCT(--($D$2:$D$135=100),--($E$2:$E$135="X"))

The -- converts the arrays of true/false values to arrays of 0s and
1s. SUMPRODUCT then multiplies the array (for which only those cases
in which both conditions are true in the same row will result in 1s)
then adds them.

 

[Kristen]

J.E. McGimpsey,
Thanks so much. It worked like a charm.

 

[RagDyer]

John,

What exactly is the advantage of this form of Sumproduct (,--)over the (*)
version, since you are actually using more keystrokes ?
Is XL processing it faster or more efficiently ?
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

One way:

=SUMPRODUCT(--($D$2:$D$135=100),--($E$2:$E$135="X"))

The -- converts the arrays of true/false values to arrays of 0s and
1s. SUMPRODUCT then multiplies the array (for which only those cases
in which both conditions are true in the same row will result in 1s)
then adds them.

 

[J.E. McGimpsey]

What exactly is the advantage of this form of Sumproduct (,--)over the (*)
version, since you are actually using more keystrokes ?
Is XL processing it faster or more efficiently ?

Dave Braden and others have found that the native comma form of
SUMPRODUCT() is about 20% faster than doing the multiplication first
(and perhaps 40%+ faster than an array-entered {SUM(x*y)})

Here's what's probably going on...

SUMPRODUCT((rng1=A)*(rng2=B))

first creates two arrays of true/false, then multiplies them
together, then, finally, passes them to SUMPRODUCT().

This seems to be less efficient than SUMPRODUCT's native array
handling. Of course, it doesn't handle arrays of TRUE/FALSE, so the
double-negative coercion is necessary. One could also multiply each
array by 1 or add 0, but, as Harlan Grove has pointed out, the
double unary minus is faster.

Take a look at this thread for more discussion on the topic:

http://google.com/groups?threadm=dbraden-D8690C.09505320092003@msne
ws.microsoft.com

 

[Biff]

Very interesting thread! My question is why can't you find
that kind of detailed info in XL help? <g>

Biff

 

[Ragdyer]

Thanks for the info John.

With quite a few WBs in the 30 to 40,000 row size being used as databases,
and all being populated exclusively by links and functions, with no manual
entry at all, I am always looking for some sort of efficiency improvement.

Some of these date back to Win 3.x and XL 5.0, when I didn't have the
slightest idea of what I was doing (not that I'm any better today).

A year or so ago, after extensive exchanges with the folks around here, I
took the trouble to replace some double and /or triple Vlookups with Match
and/or Index and Match, and couldn't believe the improvement in efficiency
of some of the older ones . some over 50% faster !

I'm just too old and lazy to change to Access or a true dB.

I just like to make sure that the changes I see are more then just cosmetic
or "fad", like replacing True and False with 1 and 0.
They may require less key strokes for their original entry, but not worth
the trouble to change existing formulas in an attempt to save "something",
going forward.

 

[Alan Beban]

One could also multiply each
array by 1 or add 0, but, as Harlan Grove has pointed out, the
double unary minus is faster.

Did Harlan Grove really say this? Did I miss a thread?

Alan Beban

 

[Harlan Grove]

Did Harlan Grove really say this? Did I miss a thread?

I may have written at one point that it *may* be faster, but for me it's
advantage is purely syntactic: because of its operator precedence (highest),
it's more resistant to typos.

 

[J.E. McGimpsey]

You did qualify it:

Which is fastest? Seems to be double unary minus, which has the
advantage of also being highest in operator precedence, so it
should always bind tightest.

in this post -

http://google.com/groups?threadm=ev#bzPtjCHA.2584@tkmsftngp12

but I do remember that at the time I tested out that assertion and
found it to be true, at least compared to N() and *1...