I have a yearly running log (attached). At the bottom in cell [B88] I would like to develop a formula that gives me the number of times I ran in that specific month. Dates are in Column A and running distances are in Column B. If a distance is zero, I don't want to count it. I have attempted to solve this using the =COUNTIFS formula, but I am not able to structure it properly. Maybe =COUNTIFS is not what I should be using. Thanks for your help!
Hi
I am sure there are many ways of doing this, but this is my approach:
B88 = SUMPRODUCT((MONTH($A$2:$A$83)=ROW(A1))*($B$2:$B$83>0))
Best regards
Per Erik Midtr?d
Re: Formula to Count Events Between Specific Dates and Having Adjacent Values >0
Perfect!! Many thanks!!
Here's how to do it using COUNTIFS.
Enter the 1st of the month dates in A88:A99...
1/1/2013
2/1/2013
3/1/2013
etc
etc
12/1/2013
You can format them to display just the short month name by formatting the cells as Custom mmm.
Then, enter this formula in B88 and copy down to B99:
=COUNTIFS(A$2:A$83,">="&A88,A$2:A$83,"<="&EOMONTH(A88,0),B$2:B$83,">0")
However, I think it's easier to just leave the month names as is (text entries) and use this formula:
=SUMPRODUCT(--(TEXT(A$2:A$83,"mmm")=A88),--(B$2:B$83>0))
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks