You are here:

Excel/Formula Assistance

Advertisement


Question
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.
Second part:
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.

     A          B
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.

ANSWER: Larry,

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

=if(month(A22)<>Month(A23),1,B22+1)


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.

--
Regards,
Tom Ogilvy


---------- 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.

        A          B
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
27    
28    
29    

Thank you again for being so quick.

Larry

Answer
Larry,

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 twogilvy@msn.com

--
Regards,
Tom Ogilvy

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.