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

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

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

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

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

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