SUMIF multiple criteria in 1 range
 

Hi

I am having trouble summing a range of cells that meet multiple criteria.

I have 3 resources clin040, clin060 and clinmix. These resource codes appear
in column K. Column K also contains many other resource codes. I need to sum
all the cells in column AL that have either clin040, clin60 or clinmix in
column K.

I also need to do this over a number of sheets - clin1, clin2 clin3 etc to
clinEND, and have a total of all cells with those resources across all the
sheets.

Please help!

try this idea
=sumproduct((k2:k200={"a","b","c"})*al2:al200)

--
Don Guillett
SalesAid Software

"Mike@Q" wrote in message
...
Hi

I am having trouble summing a range of cells that meet multiple criteria.

I have 3 resources clin040, clin060 and clinmix. These resource codes
appear
in column K. Column K also contains many other resource codes. I need to
sum
all the cells in column AL that have either clin040, clin60 or clinmix in
column K.

I also need to do this over a number of sheets - clin1, clin2 clin3 etc to
clinEND, and have a total of all cells with those resources across all the
sheets.

Please help!

One way:

On each sheet in an out-of-the-way cell, say, BZ1, array-enter
(CTRL-SHIFT-ENTER, or CMD-RETURN):

=SUM(SUMIF(K:K,{"clin040","clin060","clinmix"},AL: AL))

Then on your summary sheet, enter:

=SUM(clin1:clinEND!BZ1)

where clin1 is the left-most sheet and clinEND is the right-most sheet.



In article ,
Mike@Q wrote:

Hi

I am having trouble summing a range of cells that meet multiple criteria.

I have 3 resources clin040, clin060 and clinmix. These resource codes appear
in column K. Column K also contains many other resource codes. I need to sum
all the cells in column AL that have either clin040, clin60 or clinmix in
column K.

I also need to do this over a number of sheets - clin1, clin2 clin3 etc to
clinEND, and have a total of all cells with those resources across all the
sheets.

Please help!

Mike@Q Wrote:
Hi

I am having trouble summing a range of cells that meet multiple
criteria.

I have 3 resources clin040, clin060 and clinmix. These resource codes
appear
in column K. Column K also contains many other resource codes. I need
to sum
all the cells in column AL that have either clin040, clin60 or clinmix
in
column K.

I also need to do this over a number of sheets - clin1, clin2 clin3 etc
to
clinEND, and have a total of all cells with those resources across all
the
sheets.

Please help!

If you install Longre's morefunc.xll add-in...

=SUMPRODUCT(--ISNUMBER(MATCH(THREED(clin1:clin3!K2:K60),{"clin04 0";"clin60";"clinmix"},0)),THREED(clin1:clin3!AL2: AL60))


--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=320439

HI Aladin,

How do I get Longre's morefunc.xll add-in?

Thanks

Mike


"Aladin Akyurek" wrote:


Mike@Q Wrote:
Hi

I am having trouble summing a range of cells that meet multiple
criteria.

I have 3 resources clin040, clin060 and clinmix. These resource codes
appear
in column K. Column K also contains many other resource codes. I need
to sum
all the cells in column AL that have either clin040, clin60 or clinmix
in
column K.

I also need to do this over a number of sheets - clin1, clin2 clin3 etc
to
clinEND, and have a total of all cells with those resources across all
the
sheets.

Please help!

If you install Longre's morefunc.xll add-in...

=SUMPRODUCT(--ISNUMBER(MATCH(THREED(clin1:clin3!K2:K60),{"clin04 0";"clin60";"clinmix"},0)),THREED(clin1:clin3!AL2: AL60))


--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=320439

It's available at:

http://longre.free.fr/english/index.html

Mike@Q Wrote:
HI Aladin,

How do I get Longre's morefunc.xll add-in?

Thanks

Mike


"Aladin Akyurek" wrote:


Mike@Q Wrote:
Hi

I am having trouble summing a range of cells that meet multiple
criteria.

I have 3 resources clin040, clin060 and clinmix. These resource
codes
appear
in column K. Column K also contains many other resource codes. I
need
to sum
all the cells in column AL that have either clin040, clin60 or
clinmix
in
column K.

I also need to do this over a number of sheets - clin1, clin2 clin3
etc
to
clinEND, and have a total of all cells with those resources across
all
the
sheets.

Please help!

If you install Longre's morefunc.xll add-in...


=SUMPRODUCT(--ISNUMBER(MATCH(THREED(clin1:clin3!K2:K60),{"clin04 0";"clin60";"clinmix"},0)),THREED(clin1:clin3!AL2: AL60))


--
Aladin Akyurek

------------------------------------------------------------------------
Aladin Akyurek's Profile:
http://www.excelforum.com/member.php...fo&userid=4165
View this thread:
http://www.excelforum.com/showthread...hreadid=320439




--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=320439