Countifs Formula needed to count # of dates in same month that also is in same state.

I have a spreadsheet with multiple columns. I do not want to manipulate the data in any way. On a separate spreadsheet, I need a CountIfs Formula to identify all the dates in column I that are in January,that also have Alabama in Column D.

I tried this formula, but it did not work: =COUNTIFS('DD Market Analysis'!D:D,Stats!A2,'DD Market Analysis'!I:I,MONTH('DD Market Analysis'!I:I)=1)

Note: I do not want to use an Array for this.

Thanks for the help.

Stan

Try this,
=COUNTIFS('DD Market Analysis'!D:D,Stats!A2,'DD Market Analysis'!I:I,">="&DATEVALUE("1/1/14"),'DD Market Analysis'!I:I,"<="&DATEVALUE("31/1/14")

Hey Marvin,

Thank you for the quick response, but I cannot use Arrays or Pivot Tables for this. It needs to be something that I do not have to alter the original data/spreadsheet.

Let me know if you have any other ideas.

Thanks again.

Hey,

Thanks for the feedback, this actually returned 0, when there are records in the specified column that would qualify. Any other ideas we could try?

Thanks again.

you my have to "kick it old school" and use SUMPRODUCT to force a result.

Try:
=SUMPRODUCT(--(MONTH('DD Market Analysis'!I:I)=1),--('DD Market Analysis'!D:D=Stats!A2))

(I am assuming, based on your post that 'DD Market Analysis'!I:I is the rage containing the dates, 'DD Market Analysis'!D:D is the range containing the states, and Stats!A2 is a cell containing Alabama)

That is very perceptive of you. I am getting an error on this formula though.

Any thoughts on how to correct it?

sorry, missed some single quotes.

=SUMPRODUCT(--(MONTH('DD Market Analysis'!I:I)=1),--('DD Market Analysis'!D:D='Stats'!A2))

I will be deleting the data and pasting new data in the "original data's" place monthly. Where there may be 90 rows that qualify this month, there could be 120 next month and with a pivot table that depends on specific fields being the same, it would not work.

thanks for the follow up though.