Count Formula Help

[Mighty Whites]

Hi,

I have a workbook that contains daily scores as percentages (between 0% - 100%). If the score is greater than 90% then a 'hit' is recorded. What I need is a formula that will tell me how many hits we have had in any given month.

If you need any further information then please give me a shout.

Thanks in advance

 

[onlyadrafter]

Hello,

assuming your dates are in A13 downwards, percentages are in B13 downwards.

In E12 enter 1 (for Jan) then in E13 enter

=SUMPRODUCT(--(MONTH($A$13:$A$16)=E12)*($B$13:$B$16>=90%))

copy E12 over and add 1 each time and copy E13 over.

Change refs as required.

 

[lenze]

Do you have a column where "Hit" is actually present? If so, a simple Pivot Table will give you the count. If no, you can use a Custom Field in a PT to get the same results

lenze

 

[texasalynn]

try this
=SUMPRODUCT(--($B$2:$B$44>90%),--(MONTH($A$2:$A$44)=MONTH(NOW())))

change to your layout references. Note sumproduct does not allow whole column references and the ranges must be the same size. (in Excel version 2003 and below)

HTH

 

[Weaver]

try this if your data goes over more than 1 year

=SUMPRODUCT(--(MONTH($A$1:$A$100)=MONTH(E1)),--(YEAR($A$1:$A$100)=YEAR(E1)),--($B$1:$B$100>90%))

dates in col A, Values in Colum B, month/year in E1, formula in F1
You can insert more months in column E and then copy the formula down