Countifs cell has date within month AND other cell on row is eual to specific criteria

[justinr]

I need to count the number of cells in a column with a date that falls in a specific month, but the cell in another column on the same row must equal something specific as well.
More specifically, I need to count the number of dates that fall within the month for each specific group.
The date column is formatted as 'm/d/yy h:mm AM/PM'. I do not care about the time, only the date, but do not want to have to modify that if possible.
I need to make sure that blank cells are not counted at all.

I have attached a sample. The actual file is much larger. I would like the count to appear in column F.
If correct, cell F2 would display the number 2, because only two users in Group 1 have a date listed.

Thanks for your help!
Sample date count report.xls

[sktneer]

Try this...

In F2

Please Login or Register  to view this content.

and then copy down.

I get a count of 3 for user group 1. See rows 2, 7 and 10. Or am I missing something?
See the attached.

[justinr]

Hi, I need to be able to count how many dates are within a specific month. So if running this for Jan then group one should only return "2". If running if for Nov 2014 then it would return "1".

[sktneer]

See the attached.

[bart hoekstra]

Copy formula into LOCATION F2 and copy to end of Range
=COUNTA(INDIRECT(ADDRESS(MATCH(E2,C:C,0),COLUMN(B:B))&":"&ADDRESS(IF(E3<>"",MATCH(E2+1,C:C,0)-1,COUNTA(C:C)),COLUMN(B:B))))

success

[justinr]

Sktneer, this seems to work for group one but the others are all showing 0.

[justinr]

Bart, this does not seem to be looking for the date to fall within a specific month. Group one is returning '3', though there are only 2 with a date in Jan.

[bart hoekstra]

you are quite right. Need to include month check.
sorry!

[sktneer]

Oops...
I missed the absolute reference. Sorry.
Try this in F2

Please Login or Register  to view this content.

and copy down.

[sktneer]

So you got two sorry in a row......

[bart hoekstra]

sktneer made a small mistake: You must make F1 in the formulas absolute : $F$1

It works nicely.

[justinr]

Thanks for all your help guys. The formula works in the sample but unfortunately when I modified it to work in the actual file, i am getting #VALUE!
Can you please help me? This is what I have changed the formula too: =SUMPRODUCT(--($F$2:$F$4000<>""),--(TEXT($F$2:$F$43,"MMMM")=$K$1),--($G$2:$G$4000=H2))

I made sure to align the column letters correctly in the file, they are different from the sample but they reference the same data. Could it be because I increased it to 4000 rows?
Or maybe because about 1000 of those rows are blank? I want it to automatically include new items that i add to the rows, that is why i included the extra blank rows at the end.

[sktneer]

Make all the arrays in the sumproduct formula of the same size.

[justinr]

Make all the arrays in the sumproduct formula of the same size.

Hi sktneer, I updated the formula to be =SUMPRODUCT(--($F$2:$F$4000<>""),--(TEXT($F$2:$F$4000,"MMMM")=$K$1),--($G$2:$G$4000=H2)) so that they are all 2 through 4000. Now instead of the #value! error I get a zero in the cell.
Is that what you mean by making them the same size? If so, so you know why it would be returning a zero now?

Here is the formula on row 3 - =SUMPRODUCT(--($F$2:$F$4000<>""),--(TEXT($F$2:$F$4000,"MMMM")=$K$1),--($G$2:$G$4000=H3))

[justinr]

Also, once I get the formula to work, how do I change what month it looks for? Each month, I will need it to be looking for a date within that month, but I would like to be able to tell it which month to be looking for. Thanks!!

[justinr]

I was able to get the details I needed using the following formula; =COUNTIFS(G:G,H2,F:F,">="&$N$2,F:F,"<="&$N$3)
Where N2 is start date and N3 is end date.