Starting a countif based off a date?

[nero777]

I've been a bit of a lurker for some time. Getting my excel knowledge mainly from this forum! But, I need some help and can't seem to find the solution or something similar I can work with. Any help is much appreciated!!

Current layout is:

A1 = Dates | B1 through E1 = Quarter 1, 2, 3, 4 | F1 = Count tab

In Row 2 (and so on) for Column F, I currently have a COUNTIF formula that will count the number of times a certain criteria from B2:E2 are met. However, I want to start the count based on dates.
For example:
1/1/2015 - 3/31/2015 is Quarter 1
4/1/2015 - 6/30/2015 is Quarter 2
7/1/2015 - 9/31/2015 is Quarter 3
10/1/2015 - 12/31/2015 is Quarter 4

If Cell A2 = 9/6/15, then I want the formula to start counting from Column D/Row 2 or D2 and continue counting the next Quarter, but not the quarters before it. The reason for this is the other quarters (B2, C2) may have a value to show important data, but I only want to start the count from the date in A2. Keep in mind, the quarters may extend into 2016 quarters, so Column B - E will be Quarter 1 - 4 for 2015 and added would be Column F - I will be Quarter 1 - 4 for 2016, then have Column J as the count tab.


Thanks so much!

 

[admiral100]

Hi ,

Not the most elegant formula ......
=IF(MONTH(A2)<=3,COUNT(B2:E2),IF(MONTH(A2)<=6,COUNT(C2:E2),IF(MONTH(A2)<=9,COUNT(D2:E2),COUNT(E2:E2))))

 

[admiral100]

another way ...

But you need to change the header like :

B1= 31/03 C1 =30/06 D1 = 30/09 E1 = 31/12 then use more elegant formula =SUMPRODUCT((--($B$1:$E$1>=A2)))

 

[nero777]

Hi ,

Not the most elegant formula ......
=IF(MONTH(A2)<=3,COUNT(B2:E2),IF(MONTH(A2)<=6,COUNT(C2:E2),IF(MONTH(A2)<=9,COUNT(D2:E2),COUNT(E2:E2))))

Haha not the most elegant, but with some tweaking using my data... the formula worked perfectly!

Thanks for the help!