Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting multiple criteria
Sorry if this has been covered before but I can't find a solution.
I have a workbook logging accidents. I have a range named "arealog" detailing which department the accident occurred in i.e. forge, bending, painting, bushing etc. I also have a range named "typelog" detailing the type of accident i.e. bruise, sprain, laceration etc. On a sperate sheet of the same workbook I want to be able to count the number of "Sprains" in the "Bending" department. something like SUM((arealog=”bending”)IF(typelog=”sprain”)) Hope this makes sense Thanks in anticipation Martin |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting multiple criteria
=SUMPRODUCT(--(arealog="bending"),--(typelog="sprain"))
note that your named ranges need to have the same dimension I would personally use =SUMPRODUCT(--(arealog=A2),--(typelog=B2)) where A2 and B2 are 2 cells where you would put the criteria, that way you don't need to edit the formula itself if/when you change the criteria -- Regards, Peo Sjoblom " wrote in message ... Sorry if this has been covered before but I can't find a solution. I have a workbook logging accidents. I have a range named "arealog" detailing which department the accident occurred in i.e. forge, bending, painting, bushing etc. I also have a range named "typelog" detailing the type of accident i.e. bruise, sprain, laceration etc. On a sperate sheet of the same workbook I want to be able to count the number of "Sprains" in the "Bending" department. something like SUM((arealog=”bending”)IF(typelog=”sprain”)) Hope this makes sense Thanks in anticipation Martin |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting multiple criteria
On Sep 29, 8:18*pm, "Peo Sjoblom" wrote:
=SUMPRODUCT(--(arealog="bending"),--(typelog="sprain")) note that your named ranges need to have the same dimension I would personally use =SUMPRODUCT(--(arealog=A2),--(typelog=B2)) where A2 and B2 are 2 cells where you would put the criteria, that way you don't need to edit the formula itself if/when you change the criteria -- Regards, Peo Sjoblom " wrote in message ... Sorry if this has been covered before but I can't find a solution. I have a workbook logging accidents. I have a range named "arealog" detailing which department the accident occurred in i.e. forge, bending, painting, bushing etc. I also have a range named "typelog" detailing the type of accident i.e. bruise, sprain, laceration etc. On a sperate sheet of the same workbook I want to be able to count the number of "Sprains" in the "Bending" department. something like * SUM((arealog=”bending”)IF(typelog=”sprain”)) Hope this makes sense Thanks in anticipation Martin Thanks Peo that worked fine Martin |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting multiple criteria
Hi Peo,
A quick question - why use =SUMPRODUCT(--(arealog=A2),--(typelog=B2)) instead of =SUMPRODUCT(N(arealog=A2),N(typelog=B2)) ? -- Thanks, Shane Devenshire "Peo Sjoblom" wrote: =SUMPRODUCT(--(arealog="bending"),--(typelog="sprain")) note that your named ranges need to have the same dimension I would personally use =SUMPRODUCT(--(arealog=A2),--(typelog=B2)) where A2 and B2 are 2 cells where you would put the criteria, that way you don't need to edit the formula itself if/when you change the criteria -- Regards, Peo Sjoblom " wrote in message ... Sorry if this has been covered before but I can't find a solution. I have a workbook logging accidents. I have a range named "arealog" detailing which department the accident occurred in i.e. forge, bending, painting, bushing etc. I also have a range named "typelog" detailing the type of accident i.e. bruise, sprain, laceration etc. On a sperate sheet of the same workbook I want to be able to count the number of "Sprains" in the "Bending" department. something like SUM((arealog=€¯bending€¯)IF(typelog=€¯sprain€¯ )) Hope this makes sense Thanks in anticipation Martin |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting multiple criteria
While it wouldn't have made a difference in this case;
It's faster, you save nested function calls and it doesn't always work the same way. For instance =SUMPRODUCT(--(A1:A5)) and =SUMPRODUCT(N(A1:A5)) if A1: A5 holds TRUE TRUE TRUE TRUE TRUE does not return the same result I use N with INDIRECT like in =SUMPRODUCT(--(T(INDIRECT("'Sheet"&{1,2,3}&"'!B1"))="x"),N(INDIR ECT("'Sheet"&{1,2,3}&"'!A1"))) which would be a 3D SUMPRODUCT and where if I used a -- instead of N it would return an error -- Regards, Peo Sjoblom "ShaneDevenshire" wrote in message ... Hi Peo, A quick question - why use =SUMPRODUCT(--(arealog=A2),--(typelog=B2)) instead of =SUMPRODUCT(N(arealog=A2),N(typelog=B2)) ? -- Thanks, Shane Devenshire "Peo Sjoblom" wrote: =SUMPRODUCT(--(arealog="bending"),--(typelog="sprain")) note that your named ranges need to have the same dimension I would personally use =SUMPRODUCT(--(arealog=A2),--(typelog=B2)) where A2 and B2 are 2 cells where you would put the criteria, that way you don't need to edit the formula itself if/when you change the criteria -- Regards, Peo Sjoblom " wrote in message ... Sorry if this has been covered before but I can't find a solution. I have a workbook logging accidents. I have a range named "arealog" detailing which department the accident occurred in i.e. forge, bending, painting, bushing etc. I also have a range named "typelog" detailing the type of accident i.e. bruise, sprain, laceration etc. On a sperate sheet of the same workbook I want to be able to count the number of "Sprains" in the "Bending" department. something like SUM((arealog="bending")IF(typelog="sprain")) Hope this makes sense Thanks in anticipation Martin |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting multiple criteria
Thanks,
-- Thanks, Shane Devenshire "ShaneDevenshire" wrote: Hi Peo, A quick question - why use =SUMPRODUCT(--(arealog=A2),--(typelog=B2)) instead of =SUMPRODUCT(N(arealog=A2),N(typelog=B2)) ? -- Thanks, Shane Devenshire "Peo Sjoblom" wrote: =SUMPRODUCT(--(arealog="bending"),--(typelog="sprain")) note that your named ranges need to have the same dimension I would personally use =SUMPRODUCT(--(arealog=A2),--(typelog=B2)) where A2 and B2 are 2 cells where you would put the criteria, that way you don't need to edit the formula itself if/when you change the criteria -- Regards, Peo Sjoblom " wrote in message ... Sorry if this has been covered before but I can't find a solution. I have a workbook logging accidents. I have a range named "arealog" detailing which department the accident occurred in i.e. forge, bending, painting, bushing etc. I also have a range named "typelog" detailing the type of accident i.e. bruise, sprain, laceration etc. On a sperate sheet of the same workbook I want to be able to count the number of "Sprains" in the "Bending" department. something like SUM((arealog=€¯bending€¯)IF(typelog=€¯sprain€¯ )) Hope this makes sense Thanks in anticipation Martin |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting Multiple Criteria | Excel Worksheet Functions | |||
counting with multiple criteria | Excel Worksheet Functions | |||
Counting by multiple criteria | Excel Worksheet Functions | |||
Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions | |||
Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions |