Count the number of values having zero and below zero according the drop list text

[fmoiduf]

Sir,

I have table containing Observation and Score.

i made a drop down list according the text of observation column. I like to count number of scores comes zero and below zero according the text (observation) [TABLE="width: 500"]
<tbody>[TR]
[TD]Observation[/TD]
[TD]Score[/TD]
[/TR]
[TR]
[TD]Health_Safety-Electrical<strike></strike>
[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Health_Safety-Confined Space<strike></strike>
[/TD]
[TD]-2[/TD]
[/TR]
[TR]
[TD]Health_Safety-Access & Egress<strike></strike>
[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Health_Safety-Electrical<strike></strike>
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Health_Safety-Access & Egress<strike></strike>
[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Health_Safety-Confined Space<strike></strike>
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Health_Safety-Access & Egress<strike></strike>
[/TD]
[TD]-1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I made a drop list ( observation column text ) in excel sheet, so once I changing the text in drop down list . Score need to count according to observation which is below zero.


Thanks in advance

 

[gaz_chops]

Hi,

Try

=COUNTIFS(A2:A8,D2,B2:B8,"<="&0)

A2:A8 is the range of "Observations"
D2 is the drop down list text
B2:B8 is the range of "Score"

Gaz

 

[Fluff]

Something like
=COUNTIFS(A2:A8,E2,B2:B8,"<=0")
Change E2 to the cell that has the dropdown