counting multiple criteria

[durbat2]

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

 

[Peo Sjoblom]

=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

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

 

[durbat2]

=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


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

 

[ShaneDevenshire]

Hi Peo,

A quick question - why use

=SUMPRODUCT(--(arealog=A2),--(typelog=B2))
instead of
=SUMPRODUCT(N(arealog=A2),N(typelog=B2))
?

 

[Peo Sjoblom]

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(INDIRECT("'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]

Thanks,
--
Thanks,
Shane Devenshire

Hi Peo,

A quick question - why use

=SUMPRODUCT(--(arealog=A2),--(typelog=B2))
instead of
=SUMPRODUCT(N(arealog=A2),N(typelog=B2))
?