Formula to count specific items in a certain date range.

[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!

 

[T. Valko]

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

 

[DILipandey]

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

 

[Rocky]

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 -

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

 

[Ms K King]

That did the trick! Thank you!

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

 

[Ms K King]

I am currently working on learning more about Excel. I haven't gotten to how
these types of functions work yet. Thank you, though!

 

[T. Valko]

You're welcome. Thanks for the feedback!