Hi Ryan
This probably looks pretty messy but it will give you what you need
=COUNTIF(OFFSET($A$1,MATCH(N1,MONTH($A$1:$A$20),0)-1,8,MAX((MONTH($A$1:$A$20)=N1)*(ROW($A$1:$A$20)))-MATCH(N1,MONTH($A$1:$A$20),0)+1,3),O1
The OFFSET function selects the range to searc
$A$1 - This is the cell to be offset from
MATCH(N1,MONTH($A$1:$A$20),0)-1 - finds the first row with the desired month and offset by one less than that number
8 - offset by this many columns to get to column
MAX((MONTH($A$1:$A$20)=N1)*(ROW($A$1:$A$20)))-MATCH(N1,MONTH($A$1:$A$20),0)+1 - finds the last row with that month, subtracts the first row with that month and adds one. This sets the number of rows in the range
3 - Sets the range to 3 columns wid
The COUNTIF function serches for the desired text within the selected range
The formula needs to be array entered (hit Control-Shift-Enter instead of Enter) the formula will appear with { } around it in the formula bar
All of the $A$1:$A$20 references must be the same, change this to the actual range you need. you should use an absolute refernce style so the range won't change when you copy the formula
N1 contains the month number to check (1-jan, 2-feb, ...) O1 contains the text to search for. Change N1 and O1 to whatever cell references you prefe
Repost if you need any help
Good Luck
Mark Graesse
(e-mail address removed)
Boston M
----- Ryan wrote: ----
Help
I'm new to excel and need formula help. I have dates in column A an
text in column I and K. I want a formula to find out how many time
an item occurs per month in column I and K.
thank
Rya
