Counting the Excced occurance...

[Guest]

I have a spreadsheet listing various chemical concentrations over several
months and I need to determine how manytimes the allowable limites have been
exceeded. I am struggling to get a formula to do what I want.... I have
pieced together the following formula:

=IF((MONTH($H$2)>=5)*AND(MONTH($H$2)<=11),SUMPRODUCT(--(TEXT($C$7:$C$54,"yyyymm")=$H$2),--(J7:J54>400)),"Winter")

The Cell H2 holds the Month of Interest
Column C is a list of dates (several years of dates)
and Col J is the concentration of the Chemical in question.

The First "IF" statement is present because the permitable limits change
when it is summer vs Winter and therefore the formula is determining if the
month listed in the "Month of Interest" field is between May-Nov (Summer) or
Not (Winter)

I thought the formula listed above would Determine if the month was a Summer
month (in this case the answer is yes b/c H2 = 5/1/2005...
Then the formula should locate all the records that have the month and year
= 05/2005...
Then the Formula would Sum all the selected records values that were located
in Col J...

Actually this isn't really what I want but i cant even get it to acomplish
that I thought it should be doing...

In reality i dont want it to sum all the cells that meet the criteria in Col
J. What I really want is the formula to return the number of Times the Cells
in Col J exceeded the limit (400 in this example). in orther words the
number of times there was a violation within the month of Interest...

Any suggestions???

 

[Guest]

try:

=Sumproduct(--(month($C$7:$C$54)>=$H$2),--(month($C$7:$C$54)<=$H$3),--(J7:J54>H4))

With H2/H3 holding month start/ends ( 5,11 for summer),(12,4 for winter)
H4 is your seasonal limit

 

[Guest]

.... my other formula won't work for winter months, so try:

For winter:

=SUMPRODUCT(--(MONTH($C$7:$C$54)={1,2,3,4,12}*(YEAR(--$C$7:$C$54)=2007)*($J$7:$J$54>$H$4)))

For summer:

=SUMPRODUCT(--(MONTH($C$7:$C$54)={5,6,7,8,9,10,11}*(YEAR(--$C$7:$C$54)=2007)*($J$7:$J$54>$H$4)))