COUNTIFS - Is this correct?

[monkeyharris]

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]

Are these dates in column E all in one year or are you looking for dates in September of any year?

 

[monkeyharris]

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

 

[steve the fish]

Ok do you have that year stored anywhere? Or we using the current year?

 

[steve the fish]

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]

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]

FANTASTIC Works a treat and a massive thanks

 

[monkeyharris]

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

 

[monkeyharris]

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]

Days? You mean months?