QUESTION: I am using Excel 2007.
I would like excel (using a formula)to count the number of entries I make per month when certain conditions are met. I have provided an example and spreadsheet layout below:
If A23 value is any date in Feb and B22 is greater than 1, then B23 should be 1. Now, the second entry I make for Feb in B24, regardless of the date, should be 2, and so on.
Then, if A27 value is any date in Apr and B26 is greater than 1, then B27 should be 1. The second entry I make for Apr in B28, regardless of the date, should be 2 and so on. See below.
22 31-Jan-16 33
23 3-Feb-16 1
24 5 Feb-16 2
25 6-Feb-16 3
26 10-Feb-16 4
27 4-Apr-16 1
28 7-Apr-16 2
29 12-Apr-16 3
I would like five months used in the formula: Feb, Apr, May, Jun and Sep.
Hopefully this makes sense. Thank you.
Based on your example, I would say what you want is to start counting at 1 whenever the month in that row does not equal the month in the previous row otherwise, increment the value by 1.
So in B23 I would put
then select B23 and drag fill this formula down column B next to your data. Using what you show, I used this formula in B23 and drag filled it down and it reproduced what you show for column B starting with row B23.
---------- FOLLOW-UP ----------
QUESTION: Hello Tom, thank you for such a fast response. I am just getting back to you because I had to leave yesterday afternoon. Anyway, if possible, I would like to provide the following additional information.
1) If possible, I would like the formula you sent me to only apply if the months in column A are specifically Feb, Apr, May, Jun or Sep. If the months in column A are Oct, Nov, Dec or Jan, I do not want to start back at one. I would like the count to continue from the previous month. For example, if A23 is the month of Oct, then I want B23 to equal B22+1, then B24 will equal B23+1 and so on until Feb hits again, which at that time it will start back at one. See below example.
2) Also, when I drag down a formula, is it possible for the result in column B to only appear when there is an entry in column A. For example, since nothing has been keyed in A27, then nothing should appear in B27. Please see below.
22 28-Sep-16 45
23 2-Oct-16 46
24 4-Oct-16 47
25 5-Oct-16 48
26 7-Oct-16 49
Thank you again for being so quick.
If you want to send me a copy of your workbook with all your dates I will take a look and see if I can come up with a formula for you. Otherwise, I just have to guess at what I would have to take into consideration.
you can send it to firstname.lastname@example.org