Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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! |
#3
|
|||
|
|||
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! |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMIF with only one criteria cell within range | Excel Discussion (Misc queries) | |||
SUMIF across a range of worksheets | Excel Worksheet Functions | |||
Counting "rows", i.e. simultaneous criteria for multiple cells | Excel Worksheet Functions | |||
SUMIF multiple criteria | Excel Worksheet Functions | |||
Can I use a cell reference in the criteria for the sumif function. | Excel Worksheet Functions |