COUNTIFS - Is this correct?

monkeyharris

Active Member
Joined
Jan 20, 2008
Messages
346
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Are these dates in column E all in one year or are you looking for dates in September of any year?
 
Upvote 0
Ok do you have that year stored anywhere? Or we using the current year?
 
Upvote 0
This method uses current year:

=COUNTIFS(E:E,">="&DATE(YEAR(TODAY()),T3,1),E:E,"<"&DATE(YEAR(TODAY()),T3+1,1),P:P,1)
 
Upvote 0
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.
 
Upvote 0
Hi Jason, I tried the Sumproduct this morning but it gives a 0 result. The COUNTIFS worked great.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,923
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top