Forget SUMIF, COUNTIF and VLOOKUP

[Pierre Leclerc]

Hi

The SUMIF function allows you to sum a range using one criteria.

=SUMIF(a1:a11,"New York",b1:b11)

In plain English sum range B1 to B11 if in the corresponding row of A1
to A11 the value is "New York". You can use only ONE criteria

With SUMPRODUCT the same formula would look like this

=SUMPRODUCT((a1:a11="New York")*(B1:B11))

But you can also have many criterias like in:

=SUMPRODUCT((a1:a11="January")*(B1:B11="Product1")*(C1:C11="New
York")*(D111="Store1")*(E1:E11))

In plain English sum range E1 to E11 if in the corresponding row of A1
to A11 the value is "January" and if in the corresponding row of B1
to B11 the value is "Product1" and if in the corresponding row of C1
to C11 the value is "New York" and if in the corresponding row of D1
to D11 the value is "Store1" and if in the corresponding row of A1 to
A11 the value is "a"

When you discover the SUMPRODUCT formula, you can forget about COUNTIF
and SUMIF.

Also when you discover INDEX/MATCH you can replace VLOOKUP, HLOOKUP
and LOOKUP.

See these amazing formulas at work at:

http://www.excel-vba.com/index-agent.htm


Pierre Leclerc
http://www.excel-vba.com
(e-mail address removed)

 

[Aladin Akyurek]

...

When you discover the SUMPRODUCT formula, you can forget about COUNTIF
and SUMIF.

...

That's non-sense. Whenever a single condition involved, one shoul
invoke CountIf for counting and SumIf for summing. Moreover, i
efficiency (speed) is a concern, one should try to reduce 2 or mor
conditions to a single condition by concatenating them and invoke
CountIf or SumIf formula or a SumProduct or an array formula usin
Count or Sum with a lesser number of conditionals.

Also when you discover INDEX/MATCH you can replace VLOOKUP, HLOOKUP
and LOOKUP.
...

The generality of INDEX/MATCH is no reason to dispense with VLOOKUP an
HLOOKUP. And there are classes of lookup problems Index/Match canno
solve, but LOOKUP can

 

[JulieD]

Hi Aladin

could you please provide an example of what you mean by
"..... one should try to reduce 2 or more conditions to a single condition
by concatenating them ...."

Cheers
JulieD

 

[JE McGimpsey]

When you discover the SUMPRODUCT formula, you can forget about COUNTIF
and SUMIF.

Hard to imagine forgetting

=COUNTIF(A:A,"*a*")

in order to use something like

=SUMPRODUCT(--ISNUMBER(SEARCH("a",$A$1:$A$65535)))

COUNTIF is *FAR* faster, can operate on entire columns/rows, and has the
advantage of being easily interpreted.

How would you replace

=COUNTIF(A:J,"Pierre")

using SUMPRODUCT()?

Also when you discover INDEX/MATCH you can replace VLOOKUP, HLOOKUP
and LOOKUP.

I disagree. Aside from the fact that VLOOKUP is somewhat more efficient
than INDEX/MATCH in most cases, VLOOKUP, HLOOKUP and LOOKUP usually have
the advantage of being more readable. That goes a long way toward
reducing errors in spreadsheets.

 

[Bob Phillips]

I don't think this thread is worth continuing.

I think Pierre is evangelising on something he doesn't properly understand.
A couple of his responses today have demonstrated this, he suggested a -- SP
formula was wrong and should be using * (and he tested the same condition
twice for good measure), and said that Domenic's double lookup formula
didn't work whereas his SP one did. As it happened, his SP one did work, but
so did Domenic's, and as Domenic pointed out, Pierre's would not work if the
result to be returned were text.

I suggest that a generic answer to everyone is to ignore this post, and
understand that there are appropriate times to use SUMIF/COUNTIF,
appropriate times to use array formulae, appropriate times to use VLOOKUP,
and appropriate times to use SUMPRODUCT. There is no universal theory of
everything in Excel formulae .

Bob

 

[Frank Kabel]

Hi Bob
couldn't agree more <vbg>

 

[Aladin Akyurek]

I don't think this thread is worth continuing.
[...]
I suggest that a generic answer to everyone is to ignore this post,
and
understand that there are appropriate times to use SUMIF/COUNTIF,
appropriate times to use array formulae, appropriate times to use
VLOOKUP,
and appropriate times to use SUMPRODUCT. There is no universal theory
of
everything in Excel formulae .
[...]

Au contraire. Pierre Leclerc, for all we know, might believe what he
states. There are lots of spreadsheet users who end up thinking the
same, seeing 'thousends of Sumproduct or array formulas' offered by us
here and elsewhere. More often than not without concern for efficiency
and robustness. Leclerc's post is an excellent occasion to re-consider
such matters.

 

[Aladin Akyurek]

Julie,

Suppose we have

{"Item","Location","Qty";"a","loc1",58;"b","loc1",71;"a","loc2",70;"a","loc1",39;"b","loc2",73;"c","loc1",65;"c","loc1",63;"b","loc2",54}

in A1:C9 and we want a summary count per item and per location.

Instead of invoking a formula with SumProduct or Count/If or Sum/If fo
multi-conditional counting, we can concatenate Item and Location an
invoke a SumIf formula instead:

D2, copied down:

=A2&","&B2

Let F3:F5 house: {"a";"b";"c"}, the items of interest.

Let G2:H2 house: {"loc1","loc2"}

G3, copied across then down:

=SUMIF($D$2:$D$9,$F3&","&G$2,$C$2:$C$9)

would give us the multi-conditional counts we need.

This set up trades off cell space (memory) against speed (time).

 

[Bob Phillips]

Aladin,

I agree that things are always worth reconsidering, but even if Pierre
really believes what he states, and after hearing him and looking at his
website I am sure he does, there have been occasions where he was
inaccurate, and SUMPRODUCT is not the answer to life, the universe and
everything.

It is one thing to offer a solution to a particular problem, even if that is
not the fastest, quickest or most elegant, it is completely another to then
put forward a proposition as offered in the original posting.

Bob

I don't think this thread is worth continuing.
[...]
I suggest that a generic answer to everyone is to ignore this post,
and
understand that there are appropriate times to use SUMIF/COUNTIF,
appropriate times to use array formulae, appropriate times to use
VLOOKUP,
and appropriate times to use SUMPRODUCT. There is no universal theory
of
everything in Excel formulae .
[...]

Au contraire. Pierre Leclerc, for all we know, might believe what he
states. There are lots of spreadsheet users who end up thinking the
same, seeing 'thousends of Sumproduct or array formulas' offered by us
here and elsewhere. More often than not without concern for efficiency
and robustness. Leclerc's post is an excellent occasion to re-consider
such matters.

 

[Pierre Leclerc]

Bob

Here it is SUMPRODUCT replacing COUNTIF

=SUMPRODUCT((A1:E500="Pierre")*(A1:E500="Pierre"))

I don't think this thread is worth continuing.

I think Pierre is evangelising on something he doesn't properly understand.
A couple of his responses today have demonstrated this, he suggested a -- SP
formula was wrong and should be using * (and he tested the same condition
twice for good measure), and said that Domenic's double lookup formula
didn't work whereas his SP one did. As it happened, his SP one did work, but
so did Domenic's, and as Domenic pointed out, Pierre's would not work if the
result to be returned were text.

I suggest that a generic answer to everyone is to ignore this post, and
understand that there are appropriate times to use SUMIF/COUNTIF,
appropriate times to use array formulae, appropriate times to use VLOOKUP,
and appropriate times to use SUMPRODUCT. There is no universal theory of
everything in Excel formulae .

Bob

Pierre Leclerc
www.excel-vba.com
1-800-501-6760

 

[KL]

Pierre

This is shorter

=SUMPRODUCT(--(A1:E500="Pierre")

and shorter

=COUNTIF(A1:E500,"Pierre")


KL

 

[JE McGimpsey]

You're seriously suggesting that performing a 655350 cell x 655350 cell
matrix multiplication, followed by a SUM instead of a COUNTIF?????

Remember, the original question was =COUNTIF(A:J,"Pierre"), and your
formula doesn't cover the entire column.

Even one of those formulae is enough to *seriously* slow down a
worksheet recalc

A hammer's a useful tool, and it can be used to drive screws. But that's
not its best use.

 

[KL]

Just to illustrate the efficiency issue raised above - try to copy your
formula (or even my shorter version) across say 500 rows and 30 columns and
recalcuate with a timer in hand. On my system P4 1.8 GHZ 512 MB RAM the
results were as foolows:

=SUMPRODUCT((A1:E500="Pierre")*(A1:E500="Pierre")) - 54 sec.

=SUMPRODUCT(--(A1:E500="Pierre") - 20 sec.

=COUNTIF(A1:E500,"Pierre") - 10 sec.

Regards,
KL

 

[Bob Phillips]

Two points

=SUMPRODUCT(--(A1:E500="Pierre"))

does it just as well and with less comparisons (did you read the link I
provided?0,


and why replace COUNTIF, it works fine?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Pierre Leclerc]

KL

I love it...the SUMPRODUCT thing

Here is my approach (I will use a tool example like like JE). I am an
empowerer, I love to help people solve their problem. Learning one
function is easier than learning two or more functions. It is like
these multi-heads screwdrivers, most ordinary people use them rather
than sets of screwdrivers.

When I was much younger, I was a mechanic and I had very sophisticated
tools but my clients had less and I knew that they could do it anyway.
So I developed ways of doing things with ordinary tools rather than
expensive tools. And it worked.

I am not in the business of Excel to make people feel powerless, I am
in the Excel business to help people handle their own problems. My
goal is not to show that I am intelligent but to be useful. And it
works. I have help hundreds of people develop skills to handle their
own problems.

A lot of computer people had to make things happen with less usage of
memory and it ended up with the 2000 bug. We are not limited anymore
and I have never yet seen users having to handle 65,000 records of 256
fields. So efficient tools.....

Thanks again for both tips.

Regards

=SUMPRODUCT(--(A1:E500="Pierre")

Pierre Leclerc
www.excel-vba.com
1-800-501-6760

 

[JulieD]

Hi Aladin

thanks for this, makes more sense now

Cheers
JulieD