# COUNTIFS - Is this correct?

#### monkeyharris

##### Active Member
Hi all,

Hope you can help. Think i'm doing this correct but isn't working i think because of dates.

My table is A2 to P5000. I was using =COUNTIFS(E:E,T3,P:P,1)

Column E:E is dates during the year, Column P:P is number of days which is a calculation between order recieve and order due. T3 is a lookup to show the month I need to get a result for (1 to 12).

Basically if T3 is 9, i need the formlar to look i E:E for dates in September and count how many say 1 in column P:P

#### steve the fish

##### Well-known Member
Are these dates in column E all in one year or are you looking for dates in September of any year?

#### monkeyharris

##### Active Member
Just one year. I'll do a new workbook each year.

#### steve the fish

##### Well-known Member
Ok do you have that year stored anywhere? Or we using the current year?

#### steve the fish

##### Well-known Member
This method uses current year:

=COUNTIFS(E:E,">="&DATE(YEAR(TODAY()),T3,1),E:E,"<"&DATE(YEAR(TODAY()),T3+1,1),P:P,1)

#### jasonb75

##### Well-known Member
An alternative method would be

=SUMPRODUCT((MONTH(\$E\$2:\$E\$5000)=T3)*(\$P\$2:\$P\$5000=1))

Not as efficient as countifs, but with the amount of data that you have it shouldn't be an issue.

#### monkeyharris

##### Active Member
FANTASTIC Works a treat and a massive thanks

#### monkeyharris

##### Active Member
Hi Jason, I tried the Sumproduct this morning but it gives a 0 result. The COUNTIFS worked great.

#### monkeyharris

##### Active Member
Hi,
I've done some results to show 1, 2, 3, 4 & 5 days which is great. How do i change it to show anything over 5 days as one result? I tried >5 but won't work.

#### steve the fish

##### Well-known Member
Days? You mean months?