Formula to count specific items in a certain date range.

  • Thread starter Thread starter Ms K King
  • Start date Start date
M

Ms K King

I need to count items in a spreadsheet that fall into specific categories &
date ranges. The spreadsheet is for the entire year and contains about 10
categories. I can get it to count the items (I used
COUNTIF(I2:I200,"=Incomplete"), but I can't get it to exclude certain date
ranges.

Example, for the month of May I need a count of items titled "Incomplete"

I have searched many different websites but I can't seem to locate the
correct formula - I am probably just overlooking something simple! Any help
would be appreciated!
 
The spreadsheet is for the entire year

Try something like this:

A2:A200 = true Excel dates

To count May and Incomplete

=SUMPRODUCT(--(MONTH(A2:A200)=5),--(I2:I200="Incomplete"))

For other months just change the month number. May = 5
 
Hi King,

If you want to work on single criteria, then try something like:-
COUNTIF(I2:I200,"<>Incomplete"),
This will count all data except "Incomplete".
To work on multiple criterias, you should use database function e.g. DCOUNT
or DCOUNTA. Alternatively, you can use SUMPRODUCT as well.
--
Click on Yes, if it is useful.

Thanks & Best Regards,
Dilip Kumar Pandey
MBA, BCA, B.Com(Hons.)
(e-mail address removed)
(e-mail address removed)
New Delhi, India
 
Try something like this:

A2:A200 = true Excel dates

To count May and Incomplete

=SUMPRODUCT(--(MONTH(A2:A200)=5),--(I2:I200="Incomplete"))

For other months just change the month number. May = 5

--
Biff
Microsoft Excel MVP







- Show quoted text -
Click to expand...



=SUMPRODUCT((MONTH(A2:A200)=5)*(I2:I200="Incomplete"))
 
That did the trick! Thank you!

T. Valko said:
Try something like this:

A2:A200 = true Excel dates

To count May and Incomplete

=SUMPRODUCT(--(MONTH(A2:A200)=5),--(I2:I200="Incomplete"))

For other months just change the month number. May = 5
Click to expand...
 
I am currently working on learning more about Excel. I haven't gotten to how
these types of functions work yet. Thank you, though!
 
Back
Top