Excel 2007 SUMPRODUCT help

[excelnovice]

Hey,

I'm trying to create a formula where I can filter and count up a specific piece of info within a date range:

I found this thread https://www.pcreview.co.uk/forums/thread-1775891.php which i tried but unfortunately failed

This is formula I'm using
=SUMPRODUCT(--(D21998=D2016),--(A1:A1998>=B2016),--(A2:A1998<=B2017))

D Column = Status
A Column = Date

D2016 = In Progress
B2016 = Start date (01/10/2010)
B2017 = End Date (31/10/2010)

In short: I want to count how many entries for the month of october are "In Progress"

I'm just getting a Value error, I am a formula novice but this seems like a standard simply enough request it must be possible right???

Any help would be greatly appreciated.

Thanks

 

[angela.coleman]

Try using the COUNTIFS function. If this scenario, the words "IN PROGRESS" are in cell E1. This will count any items which have a date between October 1, 2010 and October 31, 2010 in Column A and "In Progress" in Column B.

=COUNTIFS(A1:A42,">=10/01/2010",A1:A42,"<=10/31/2010",B1:B42,E1)