Prev: Formula to look up Min Values in Date with 1 or more criteria
Next: Ho do i create drop down list?
From: מיכאל (מיקי) אבידן on 30 Mar 2010 10:14
There is no need for double multiplying C2:C10.
You can use a shorter formula:
=SUMPRODUCT(((A2:A10="Bob")+(A2:A10<>"Bob")*(B2:B10="Feb-10"))*C2:C10)
Micky


"Michael_R" wrote:

> I have the following table:
>
> Name Month Sales
> Mike Feb-10 1
> Bob Mar-10 2
> Alex Mar-10 4
> Bob Mar-10 8
> Bob Feb-10 16
>
> In order to get all sales of Bob plus all sales (by anybody else) in Feb-10
> (result = 27), I devised the following formula:
>
> =SUMIF(Name,"Bob",Sales)+SUMPRODUCT((Name<>"Bob")*(Month="Feb-10"),Sales)
>
> Question:
> Is there a (simple) way to produce the required result using only one
> SUMPRODUCT ie getting rid of the SUMIF?
From: Michael_R on 30 Mar 2010 10:24
Micky, this is an amazing formula. Thanks!

"מיכאל (מיקי) אבידן" wrote:

> There is no need for double multiplying C2:C10.
> You can use a shorter formula:
> =SUMPRODUCT(((A2:A10="Bob")+(A2:A10<>"Bob")*(B2:B10="Feb-10"))*C2:C10)
> Micky
>
>
> "Michael_R" wrote:
>
> > I have the following table:
> >
> > Name Month Sales
> > Mike Feb-10 1
> > Bob Mar-10 2
> > Alex Mar-10 4
> > Bob Mar-10 8
> > Bob Feb-10 16
> >
> > In order to get all sales of Bob plus all sales (by anybody else) in Feb-10
> > (result = 27), I devised the following formula:
> >
> > =SUMIF(Name,"Bob",Sales)+SUMPRODUCT((Name<>"Bob")*(Month="Feb-10"),Sales)
> >
> > Question:
> > Is there a (simple) way to produce the required result using only one
> > SUMPRODUCT ie getting rid of the SUMIF?
 | 
Pages: 1
Prev: Formula to look up Min Values in Date with 1 or more criteria
Next: Ho do i create drop down list?