# Count Formula Help

#### Mighty Whites

##### New Member
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.

### Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

##### Well-known Member
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

##### Legend
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

##### Well-known Member
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

##### Well-known Member
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

Replies
2
Views
83
Replies
14
Views
204
Replies
0
Views
91
Replies
3
Views
70
Replies
5
Views
203

1,191,322
Messages
5,985,976
Members
439,994
Latest member
lalkelalke04

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back