Sumif with criteria list
 

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" wrote in message
...
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 ,
Brian Barbre 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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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.