# 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?

1,078,437
Messages
5,340,271
Members
399,361
Latest member
Linford

### This Week's Hot Topics

• Problem with Radio Button's format control
I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
• Last Display on userform to a Listbox
[CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
• Rename and move files to a new location
Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
• Help with True/False Formula
Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
• Clear extra characters from a provided range of cells
Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
• Help with Current and highest streaks
Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...