MrExcel Publishing
Your One Stop for Excel Tips & Solutions

sumif w/ 2 conditions


Posted by mcarter973 on July 24, 2001 12:37 PM

i have the following problem:

Column A has types of widgets
Column B has the quantity bought/sold - the amt sold is a negative number.

I want to sum all Type A widgets bought and sold (in separate columns) and do the same for Type B and so on.

Here's the formula that I used:

Type A - Purch.

sum (if(a1:a40="A",if(b1:b40>0,1,0)0))

Type A - Sold

sum (if(a1:a40="A",if(b1:b40<0,1,0)0))

What am I missing?

Thanks.


Posted by Aladin Akyurek on July 24, 2001 12:43 PM


===============

In C1 enter: =SUMPRODUCT((A1:A40="A")*(B1:B40>0)*(B1:B40))

In D1 enter: =SUMPRODUCT((A1:A40="A")*(B1:B40 < 0)*(B1:B40))

Aladin

Posted by IML on July 24, 2001 12:45 PM

For type A purchased, try
=SUM((A1:A40="a")*(B1:B40>0))

for type a returned try
=SUM((A1:A40="a")*(B1:B40>0))

hit enter while control and shift are depressed to avoid a pesky #value! error.

If you a large spreadsheet, array formulas may slow it down quite a bit.

Good luck.

Posted by Mark W. on July 24, 2001 12:45 PM

{=SUM((A1:A40="A")*(B1:B40>0))}
{=SUM((A1:A40="A")*(B1:B40<0))}

These are array formulas which must be entered
using the Control+Shift+Enter key combination.

Posted by Mark W. on July 24, 2001 12:51 PM

Noticed that you using SUMPRODUCT() alot lately...

...with only 1 arugment. Were you aware that the
Help topic specifies that at least 2 are required.
There must be some undocumented "feature" that
permits only 1.

Posted by iml on July 24, 2001 12:51 PM

OOPS

Those provide the count. For sums try

=SUM((A1:A40="a")*(B1:B40>0)*(B1:B40))
and
=SUM((A1:A40="a")*(B1:B40>0)*(B1:b40))

Posted by Mark W. on July 24, 2001 12:52 PM

OOPS^2

Posted by Aladin Akyurek on July 24, 2001 1:05 PM

Re: Noticed that you using SUMPRODUCT() alot lately...

Yes, much "enamored of" it lately. Also, I knew perfectly well that you would notice. The only thing that I badly need is to test it against its array brothers with data where the latter are seen as culprit of slow performance.

Aladin

==========

Posted by Mark W. on July 24, 2001 1:14 PM

Re: Noticed that you using SUMPRODUCT() alot lately...

I understand, but perhaps it would be better
(more conformant) if you used...

=SUMPRODUCT((A1:A40="A")+0,(B1:B40>0)+0,B1:B40)

I checked Microsoft's knowledge base and couldn't
find any info on SUMPRODUCT().

Posted by mcarter973 on July 24, 2001 1:18 PM

Re: Noticed that you using SUMPRODUCT() alot lately...

If Widget A is not a numerical value, how could you multiply A by the quantity? Also, I hit CSE after my original formula (see orig. post) and it appears to be counting the cells as opposed to summing the data.

Posted by Mark W. on July 24, 2001 1:23 PM

Re: Noticed that you using SUMPRODUCT() alot lately...

We're not multiplying by widget "A", but rather by
the boolean results of a conditional test for
widget A. In Excel TRUE is equivalent to 1 and
FALSE is equivalent to 0. It's these 0's and 1's
that we're multiplying. : I understand, but perhaps it would be better

Posted by Aladin Akyurek on July 24, 2001 1:36 PM

Re: Noticed that you using SUMPRODUCT() alot lately...

As with array formulas, the last term does the required coercing. Whence my omission of +0. I checked Microsoft's knowledge base and couldn't

I take this remark as a hint for the question "How did I come upon this idea?" Someone who posted a question included a formula with this function in his post. The question was about whether you can use named (dynamic) ranges with this function. While replying to this question, I just had a simple thought: "Yes, a formula with SUMPRODUCT could be a nice sister to array formulas." And, after that, just started testing how it behaves in those situations where we normally use array-formulas. Human ognition is a lazy computational engine as the jargon has it: it will rather not think if it can do with some routine that works. I'm willing to admit this as an explanation why *I* didn't know or think much earlier of applying SUMPRODUCT the way I started doing after that incident.

Aladin

Posted by Mark W. on July 24, 2001 1:40 PM

In response to your 2nd question

> ...it appears to be counting the cells as
> opposed to summing the data...

The quantities themselves need to be included as
a factor in the array formula. As IML pointed
out in his "OOPS" posting you should be using...

{=SUM((A1:A40="A")*(B1:B40>0)*(B1:B40))}

...if you want to sum the quantity. : I understand, but perhaps it would be better

Posted by Mark W. on July 25, 2001 8:37 AM

Back to ya...

> As with array formulas, the last term does the
> required coercing. Whence my omission of +0.

Ah, but only if you combine arguments
with an arithmetic operator such as...

=SUMPRODUCT((A1:A40="A")*(B1:B40>0)*B1:B40)

or

=SUMPRODUCT((A1:A40="A")*(B1:B40>0),B1:B40)

...but, only the latter conforms to the
SUMPRODUCT Help topic that suggests that as
a minimum SUMPRODUCT() should have 2 arguments.

Posted by Aladin Akyurek on July 25, 2001 9:00 AM

Re: Back to ya...

Yeah. However, the following works too:

=SUMPRODUCT((A1:A5=1)+0) [ 2 args? ]

just like its brother {=SUM((A1:A5=1)+0)}

I think in a substantial number of cases SUMPRODUCT will do the job.

Since this function just like FREQUENCY treats its range args as arrays, the question of which one, the sister or the brother, runs become hotter. I'd expect that, more or less, the same routines/algorithms must have been used in SUMPRODUCT and array formulas. Don't you think? [ I need Andonny's data to carry out the acid test. ]