Fiscal year COUNTIF formula

[mprusin]

Need help tweaking my formula...will work on one spreadsheet but not my current one.

Current formula is =COUNTIFS($G$7:$AJ$115,"AK",$D$7:$D$115,">"&DATEVALUE("9/30/17"),$D$7:$D$115,"<"&DATEVALUE("1/1/18"))

G7:AJ115 is the full range (all State abbreviations are located here)

D7:D115 is the full range of Rpt (report date)

I need my formula to look through G7:AJ115 for "AK" and then tell me how many times "AK" shows up in either 1,2,3 or 4th QTRs. The DATEVALUE portion above is to catch all 1ST QTR "AK".

Excel is showing the error is with my DATEVALUE and dates. I don't know why. I've tried CTRL H to fit date format, that didn't work

Snipet of full spreadsheet is below.

B C D E F G
[TABLE="width: 1280"]
<tbody>[TR]
[TD]Class[/TD]
[TD]16 Day[/TD]
[TD]Rpt[/TD]
[TD]Start[/TD]
[TD]End[/TD]
[TD="colspan: 14"]Quota Allocation[/TD]
[/TR]
[TR]
[TD]001[/TD]
[TD]27-Sep-17[/TD]
[TD]10/13/2017[/TD]
[TD]10/14/17[/TD]
[TD]10/27/17[/TD]
[TD]PA[/TD]
[TD]AR[/TD]
[TD]PA[/TD]
[TD]CT[/TD]
[TD]CT[/TD]
[TD]WI[/TD]
[TD]NY[/TD]
[TD]RI[/TD]
[TD]NJ[/TD]
[TD]PA[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]002[/TD]
[TD]15-Nov-17[/TD]
[TD]12/1/2017[/TD]
[TD]12/02/17[/TD]
[TD]12/15/17[/TD]
[TD]IN[/TD]
[TD]PA[/TD]
[TD]PA[/TD]
[TD]PA[/TD]
[TD]PA[/TD]
[TD]PA[/TD]
[TD]PA[/TD]
[TD]CT[/TD]
[TD]OH[/TD]
[TD]NY[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]003[/TD]
[TD]03-Jan-18[/TD]
[TD]1/19/2018[/TD]
[TD]01/20/18[/TD]
[TD]02/02/18[/TD]
[TD]ND[/TD]
[TD]IA[/TD]
[TD]PA[/TD]
[TD]PA[/TD]
[TD]PA[/TD]
[TD]PA[/TD]
[TD]PA[/TD]
[TD]PA[/TD]
[TD]PA[/TD]
[TD]VT[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]004[/TD]
[TD]21-Feb-18[/TD]
[TD]3/9/2018[/TD]
[TD]03/10/18[/TD]
[TD]03/23/18[/TD]
[TD]NJ[/TD]
[TD]SD[/TD]
[TD]ND[/TD]
[TD]PA[/TD]
[TD]PA[/TD]
[TD]ME[/TD]
[TD]CT[/TD]
[TD]NH[/TD]
[TD]PA[/TD]
[TD]MA[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]005[/TD]
[TD]14-Mar-18[/TD]
[TD]3/30/2018[/TD]
[TD]03/31/18[/TD]
[TD]04/13/18[/TD]
[TD]PA[/TD]
[TD]PA[/TD]
[TD]VT[/TD]
[TD]PA[/TD]
[TD]CT[/TD]
[TD]CT[/TD]
[TD]OH[/TD]
[TD]MD[/TD]
[TD]NY[/TD]
[TD]NH[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]006[/TD]
[TD]18-Apr-18[/TD]
[TD]5/4/2018[/TD]
[TD]05/05/18[/TD]
[TD]05/18/18[/TD]
[TD]SD[/TD]
[TD]ND[/TD]
[TD]ME[/TD]
[TD]CT[/TD]
[TD]CT[/TD]
[TD]NGB[/TD]
[TD]NGB[/TD]
[TD]MD[/TD]
[TD]NGB[/TD]
[TD]NGB[/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]007[/TD]
[TD]16-May-18[/TD]
[TD]6/1/2018[/TD]
[TD]06/02/18[/TD]
[TD]06/15/18[/TD]
[TD]AR[/TD]
[TD]NH[/TD]
[TD]NGB[/TD]
[TD]NGB[/TD]
[TD]NY[/TD]
[TD]NH[/TD]
[TD]MI[/TD]
[TD]NJ[/TD]
[TD]NJ[/TD]
[TD]NJ[/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]008[/TD]
[TD]20-Jun-18[/TD]
[TD]7/6/2018[/TD]
[TD]07/07/18[/TD]
[TD]07/20/18[/TD]
[TD]PA[/TD]
[TD]PA[/TD]
[TD]MD[/TD]
[TD]MD[/TD]
[TD]MD[/TD]
[TD]NJ[/TD]
[TD]NJ[/TD]
[TD]NJ[/TD]
[TD]NJ[/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]009[/TD]
[TD]18-Jul-18[/TD]
[TD]8/3/2018[/TD]
[TD]08/04/18[/TD]
[TD]08/17/18[/TD]
[TD]CT[/TD]
[TD]CT[/TD]
[TD]NH[/TD]
[TD]MD[/TD]
[TD]MD[/TD]
[TD]MD[/TD]
[TD]MD[/TD]
[TD]OH[/TD]
[TD]NY[/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]010[/TD]
[TD]22-Aug-18[/TD]
[TD]9/7/2018[/TD]
[TD]09/08/18[/TD]
[TD]09/21/18[/TD]
[TD]ME[/TD]
[TD]ME[/TD]
[TD]MD[/TD]
[TD]MD[/TD]
[TD]MD[/TD]
[TD]MD[/TD]
[TD]NY[/TD]
[TD]NY[/TD]
[TD]NJ[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody><colgroup><col><col span="4"><col><col span="10"><col><col span="2"></colgroup>[/TABLE]

 

[Jonmo1]

Try using DATE instead of DateValue
DATE(yyyy,mm,dd)

So change
DATEVALUE("9/30/17")
to
DATE(2017,9,30)

 

[Jonmo1]

Also, should have seen this first...

Countifs requires all the ranges to be of similar dimensions.
You have $G$7:$AJ$115 which is multi column and multi row
Then $D$7:$D$115 which is just a single column.
That won't work.

You will probably need to use sumproduct, hang on.

 

[Jonmo1]

Try

=SUMPRODUCT(($G$7:$AJ$115="AK")*($D$7:$D$115>DATE(2017,9,30))*($D$7:$D$115<DATE(2018,1,1)))

 

[mprusin]

IT WORKS!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! I spent a full day off an on trying to figure this out. My HERO!

 

[Jonmo1]

You're welcome.