Sumif with criteria list

Hi Brian,

a couple of options:

=SUMPRODUCT(SUMIF(B1:E1,{"Company A","Company B"},B2:E2))

=SUMPRODUCT((B1:E1={"Company A";"Company B"})*B2:E2)

The fixed arrays can be replaced by range references: in the 2nd formula the
range must be vertical (or horizontal with TRANSPOSE function).

Regard,
KL


"Brian Barbre" <[email protected]> wrote in message
news:[email protected]...
>I have a row of data that I want to sum if the corresponding collumns are
>one
> of several departments. I can't figure out how to use multiple criteria
> with
> the sumif function and the data is not in typical DB format so I can't use
> the DSUM function. Does anyone know how to solve this problem?
>
> Thanks in advance,
>
> Brian
>
> Sample
> Company A Company B Company C Company D
> Assets 15 5 20 25
>
> Question: What are the assets for companies A and B.
> My actual situation could have 15 different search requirements.
>
>

Try...

=SUMPRODUCT(--(ISNUMBER(MATCH($B$1:$E$1,{"Company A","Company
B"},0))),B2:E2)

OR

=SUMPRODUCT(--(ISNUMBER(MATCH($B$1:$E$1,$G$1:$G$2,0))),B2:E2)

....where G1:G2 contains your company names.

Hope this helps!

In article <[email protected]>,
Brian Barbre <[email protected]> wrote:

> I have a row of data that I want to sum if the corresponding collumns are one
> of several departments. I can't figure out how to use multiple criteria with
> the sumif function and the data is not in typical DB format so I can't use
> the DSUM function. Does anyone know how to solve this problem?
>
> Thanks in advance,
>
> Brian
>
> Sample
> Company A Company B Company C Company D
> Assets 15 5 20 25
>
> Question: What are the assets for companies A and B.
> My actual situation could have 15 different search requirements.

Thanks KL,
That solved my problem. I was actually able to just enclose the SUMIF
formula I alread had with the SUMPRODUCT formula. Is the reason why the
SUMPRODUCT formula works because it is able to handle arrays where as the
SUMIF is not?

"KL" wrote:

> Hi Brian,
>
> a couple of options:
>
> =SUMPRODUCT(SUMIF(B1:E1,{"Company A","Company B"},B2:E2))
>
> =SUMPRODUCT((B1:E1={"Company A";"Company B"})*B2:E2)
>
> The fixed arrays can be replaced by range references: in the 2nd formula the
> range must be vertical (or horizontal with TRANSPOSE function).
>
> Regard,
> KL
>
>
> "Brian Barbre" <[email protected]> wrote in message
> news:[email protected]...
> >I have a row of data that I want to sum if the corresponding collumns are
> >one
> > of several departments. I can't figure out how to use multiple criteria
> > with
> > the sumif function and the data is not in typical DB format so I can't use
> > the DSUM function. Does anyone know how to solve this problem?
> >
> > Thanks in advance,
> >
> > Brian
> >
> > Sample
> > Company A Company B Company C Company D
> > Assets 15 5 20 25
> >
> > Question: What are the assets for companies A and B.
> > My actual situation could have 15 different search requirements.
> >
> >
>
>
>

Well, not exactly. SUMIF does return an array if the second argument is an
array. The question is how do you sum the members of that array. You can, of
course, use the SUM function, but will have to confirm it by
Ctrl+Shift+Enter, whereas SUMPRODUCT doesn't need to be array entered to
handle arrays.

KL

"Brian Barbre" <[email protected]> wrote in message
news:[email protected]...
> Thanks KL,
> That solved my problem. I was actually able to just enclose the SUMIF
> formula I alread had with the SUMPRODUCT formula. Is the reason why the
> SUMPRODUCT formula works because it is able to handle arrays where as the
> SUMIF is not?
>
> "KL" wrote:
>
>> Hi Brian,
>>
>> a couple of options:
>>
>> =SUMPRODUCT(SUMIF(B1:E1,{"Company A","Company B"},B2:E2))
>>
>> =SUMPRODUCT((B1:E1={"Company A";"Company B"})*B2:E2)
>>
>> The fixed arrays can be replaced by range references: in the 2nd formula
>> the
>> range must be vertical (or horizontal with TRANSPOSE function).
>>
>> Regard,
>> KL
>>
>>
>> "Brian Barbre" <[email protected]> wrote in message
>> news:[email protected]...
>> >I have a row of data that I want to sum if the corresponding collumns
>> >are
>> >one
>> > of several departments. I can't figure out how to use multiple
>> > criteria
>> > with
>> > the sumif function and the data is not in typical DB format so I can't
>> > use
>> > the DSUM function. Does anyone know how to solve this problem?
>> >
>> > Thanks in advance,
>> >
>> > Brian
>> >
>> > Sample
>> > Company A Company B Company C Company D
>> > Assets 15 5 20
>> > 25
>> >
>> > Question: What are the assets for companies A and B.
>> > My actual situation could have 15 different search requirements.
>> >
>> >
>>
>>
>>

Makes Sense...thanks for the help

Brian

"KL" wrote:

> Well, not exactly. SUMIF does return an array if the second argument is an
> array. The question is how do you sum the members of that array. You can, of
> course, use the SUM function, but will have to confirm it by
> Ctrl+Shift+Enter, whereas SUMPRODUCT doesn't need to be array entered to
> handle arrays.

I have a row of data that I want to sum if the corresponding collumns are one
of several departments. I can't figure out how to use multiple criteria with
the sumif function and the data is not in typical DB format so I can't use
the DSUM function. Does anyone know how to solve this problem?

Thanks in advance,

Brian

Sample
Company A Company B Company C Company D
Assets 15 5 20 25

Question: What are the assets for companies A and B.
My actual situation could have 15 different search requirements.

Hi Brian,

a couple of options:

=SUMPRODUCT(SUMIF(B1:E1,{"Company A","Company B"},B2:E2))

=SUMPRODUCT((B1:E1={"Company A";"Company B"})*B2:E2)

The fixed arrays can be replaced by range references: in the 2nd formula the
range must be vertical (or horizontal with TRANSPOSE function).

Regard,
KL


"Brian Barbre" <[email protected]> wrote in message
news:[email protected]...
>I have a row of data that I want to sum if the corresponding collumns are
>one
> of several departments. I can't figure out how to use multiple criteria
> with
> the sumif function and the data is not in typical DB format so I can't use
> the DSUM function. Does anyone know how to solve this problem?
>
> Thanks in advance,
>
> Brian
>
> Sample
> Company A Company B Company C Company D
> Assets 15 5 20 25
>
> Question: What are the assets for companies A and B.
> My actual situation could have 15 different search requirements.
>
>

Try...

=SUMPRODUCT(--(ISNUMBER(MATCH($B$1:$E$1,{"Company A","Company
B"},0))),B2:E2)

OR

=SUMPRODUCT(--(ISNUMBER(MATCH($B$1:$E$1,$G$1:$G$2,0))),B2:E2)

....where G1:G2 contains your company names.

Hope this helps!

In article <[email protected]>,
Brian Barbre <[email protected]> wrote:

> I have a row of data that I want to sum if the corresponding collumns are one
> of several departments. I can't figure out how to use multiple criteria with
> the sumif function and the data is not in typical DB format so I can't use
> the DSUM function. Does anyone know how to solve this problem?
>
> Thanks in advance,
>
> Brian
>
> Sample
> Company A Company B Company C Company D
> Assets 15 5 20 25
>
> Question: What are the assets for companies A and B.
> My actual situation could have 15 different search requirements.

Thanks KL,
That solved my problem. I was actually able to just enclose the SUMIF
formula I alread had with the SUMPRODUCT formula. Is the reason why the
SUMPRODUCT formula works because it is able to handle arrays where as the
SUMIF is not?

"KL" wrote:

> Hi Brian,
>
> a couple of options:
>
> =SUMPRODUCT(SUMIF(B1:E1,{"Company A","Company B"},B2:E2))
>
> =SUMPRODUCT((B1:E1={"Company A";"Company B"})*B2:E2)
>
> The fixed arrays can be replaced by range references: in the 2nd formula the
> range must be vertical (or horizontal with TRANSPOSE function).
>
> Regard,
> KL
>
>
> "Brian Barbre" <[email protected]> wrote in message
> news:[email protected]...
> >I have a row of data that I want to sum if the corresponding collumns are
> >one
> > of several departments. I can't figure out how to use multiple criteria
> > with
> > the sumif function and the data is not in typical DB format so I can't use
> > the DSUM function. Does anyone know how to solve this problem?
> >
> > Thanks in advance,
> >
> > Brian
> >
> > Sample
> > Company A Company B Company C Company D
> > Assets 15 5 20 25
> >
> > Question: What are the assets for companies A and B.
> > My actual situation could have 15 different search requirements.
> >
> >
>
>
>

Well, not exactly. SUMIF does return an array if the second argument is an
array. The question is how do you sum the members of that array. You can, of
course, use the SUM function, but will have to confirm it by
Ctrl+Shift+Enter, whereas SUMPRODUCT doesn't need to be array entered to
handle arrays.

KL

"Brian Barbre" <[email protected]> wrote in message
news:[email protected]...
> Thanks KL,
> That solved my problem. I was actually able to just enclose the SUMIF
> formula I alread had with the SUMPRODUCT formula. Is the reason why the
> SUMPRODUCT formula works because it is able to handle arrays where as the
> SUMIF is not?
>
> "KL" wrote:
>
>> Hi Brian,
>>
>> a couple of options:
>>
>> =SUMPRODUCT(SUMIF(B1:E1,{"Company A","Company B"},B2:E2))
>>
>> =SUMPRODUCT((B1:E1={"Company A";"Company B"})*B2:E2)
>>
>> The fixed arrays can be replaced by range references: in the 2nd formula
>> the
>> range must be vertical (or horizontal with TRANSPOSE function).
>>
>> Regard,
>> KL
>>
>>
>> "Brian Barbre" <[email protected]> wrote in message
>> news:[email protected]...
>> >I have a row of data that I want to sum if the corresponding collumns
>> >are
>> >one
>> > of several departments. I can't figure out how to use multiple
>> > criteria
>> > with
>> > the sumif function and the data is not in typical DB format so I can't
>> > use
>> > the DSUM function. Does anyone know how to solve this problem?
>> >
>> > Thanks in advance,
>> >
>> > Brian
>> >
>> > Sample
>> > Company A Company B Company C Company D
>> > Assets 15 5 20
>> > 25
>> >
>> > Question: What are the assets for companies A and B.
>> > My actual situation could have 15 different search requirements.
>> >
>> >
>>
>>
>>

Makes Sense...thanks for the help

Brian

"KL" wrote:

> Well, not exactly. SUMIF does return an array if the second argument is an
> array. The question is how do you sum the members of that array. You can, of
> course, use the SUM function, but will have to confirm it by
> Ctrl+Shift+Enter, whereas SUMPRODUCT doesn't need to be array entered to
> handle arrays.