Sum-If used with the And Function

[Joe H.]

Sumif(Range, Criteria, Sum Range)

A B C
Sales Rep Year Amount
Joe 2002 100.00
Joe 2002 200.00
Joe 2003 700.00
Amanda 2002 800.00

How can I use a sumif formula to add all sales for "Joe"
for only the year "2002". With the answer being obviously
$300.00.

My guess is Sumif(And(A:A="Joe",B:B=2002),C:C)

 

[Peo Sjoblom]

=SUMPRODUCT((A2:A200="Joe")*(B2:B200=2002),C2:C200)

note that for better usability replace the hard coded names and years with
cell references, that way you don't have to edit the formula e.g.

=SUMPRODUCT((A2:A200=E2)*(B2:B200=F2),C2:C200)

could look like that, note also you cannot us the full column/row as in A:A
etc..

 

[J.E. McGimpsey]

One way:

=SUMPRODUCT(--(A1:A100="Joe"),--(B1:B100=2002),C1:C100)

 

[RagDyer]

John,
Supposedly, your formula is more efficient then Peo's.

Is there any difference in efficiency between yours and this:

=SUMPRODUCT(-(A1:A100="Joe"),-(B1:B100=2002),C1:C100)


Regards,

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




One way:

=SUMPRODUCT(--(A1:A100="Joe"),--(B1:B100=2002),C1:C100)

 

[J.E. McGimpsey]

Yes, the one below is more efficient. The only reason I don't like
using a single unary minus is that it's not strictly scalable - if
you add another conditional term with a single unary minus, the
result goes negative. Using a double unary minus on each term always
returns a correct value.

 

[RagDyer]

Sounds logical.

However, to your knowledge, has anyone put some sort of measurable label on
this type of "efficiency".
I mean beyond any strictly academic context of it being more efficient
simply because it performs less actions (calculations), thus, it "has" to be
faster.

Does Redmond ever get into this sort of evaluation, so that users can
actually gauge the benefit returned in a 40 or 50,000 row WB, for the time
expended in updating or revising the older procedures.
--

Regards,

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


Yes, the one below is more efficient. The only reason I don't like
using a single unary minus is that it's not strictly scalable - if
you add another conditional term with a single unary minus, the
result goes negative. Using a double unary minus on each term always
returns a correct value.

 

[Aladin Akyurek]

Sounds logical.

However, to your knowledge, has anyone put some sort of measurable label on
this type of "efficiency".
I mean beyond any strictly academic context of it being more efficient
simply because it performs less actions (calculations), thus, it "has" to be
faster.

I did post here timing data using FastExcel a while ago.

=SUMPRODUCT(--(Conditional1),--(Conditional2),RangeToSum)

is not faster than the equivalent:

=SUMPRODUCT((Conditional1)*(Conditional2),RangeToSum)

If the conditional expressions were converted by SumProduct itself (implicit
conversion), we could have a faster function allowing us to write:

=SUMPRODUCT(Conditional1,Conditional2,RangeToSum)

The foregoing holds also for multiconditional counting.

Does Redmond ever get into this sort of evaluation, so that users can
actually gauge the benefit returned in a 40 or 50,000 row WB, for the time
expended in updating or revising the older procedures.

40 to 50k cells are a lot. The real art (of efficiency) is to restrict the
formulas operating on computed arrays to apply to relevant subranges instead
of the whole range of 40 to 50k cells. An example is in...

http://www.mrexcel.com/board2/viewtopic.php?t=62357&start=10

 

[Dave Peterson]

Just wondering if you wrapped ABS() around your formula, if that would be worth
dropping the double minuses so that it would scale better?

Or if there would be a break-even point (12 uses of -- is equivalent to one
ABS).

(As Click and Clack say: Unencumbered by the thought process....)

 

[J.E. McGimpsey]

However, to your knowledge, has anyone put some sort of measurable label on
this type of "efficiency".
I mean beyond any strictly academic context of it being more efficient
simply because it performs less actions (calculations), thus, it "has" to be
faster.

Here's a thread with some measurements:

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

 

[Ragdyer]

Thanks for the link Aladin.

That's what I'm constantly on the look out for ... that unbelievable
improvement in performance and efficiency.

<"40 to 50k cells are a lot. The real art (of efficiency) is to restrict the

formulas operating on computed arrays to apply to relevant subranges instead
of the whole range of 40 to 50k cells.">

Sounds like it came out of a programmer's text book.
It would be nice if I knew any of this stuff years ago, when I first put on
the computer hat and started making spreadsheets to keep track of our
dyeorders and shipments.
And that was in XL5.0 on a Win3.X machine.
It's grown into a giant and just about runs the entire operation, and can't
be stopped.
That's why any improvements I can glean from you guys is all well
appreciated, especially in the speed up area.
Also, the computer hat is miniscule compared to the others I wear, so the
return has to be big in relation to the effort and time expended.

 

[Ragdyer]

Even taking into consideration Word Wrap, I couldn't get that link to work.

However, seeing D.Braden in the link allowed my Google search to turn up a
31 post thread on this subject.

My personnel interpretation of the thread, with the results from Aladin and
David's calcs, are telling me (a user, not programmer), acting as my own IT
guy, not to invest any time, concerning these functions, into reworking any
of my existing large WBs.

Just repeating here what I think I mentioned to you a couple of weeks ago.
I'm constantly looking for any improvement to speed up my old, large WBs, in
order to gain any appreciable benefit, similar to that one time when I
replaced double and triple look-ups with Index and Match, and gained an
unbelievable 50% reduction in opening and recalc time.