How to count records between 2 dates and a condition Part 2.

[Phuzzy]

I found this formula on this site to count the number of closed items between two dates.

http://chandoo.org/forums/topic/hot-to-count-records-between-2-dates-and-a-condition

=+SUMPRODUCT(1*(A2:A11>=E3)*(A2:A11<=E1)*(C2:C11="Closed"))


It does not work for me.


I have successfully counted items between two dates using:

=SUMPRODUCT(('2011'!$B$2:$B$10000>=$A101)*('2011'!$B$2:$B$10000<$A102))


Where b2:b10000 is a list of dates

A101-a113 are the starting dates of months 01/01/2011, 02/01/2011...upto 01/01/2012


Now I want to count the number of Closed items between two dates. So, I modified my formula to read:


=SUMPRODUCT(('2011'!$B$2:$B$10000>=A101)*('2011'!$B$2:$B$10000<A102)*('2011'!$C$2:$C$10000="Closed"))


This is based on the example I found on this site and the result is 0. Even though I know otherwise


Then I tried....


=SUMPRODUCT(('2011'!$B$2:$B$10000>=A102)*('2011'!$B$2:$B$10000<A103)*(--(ISNUMBER(SEARCH("Closed",'2011'!$C$2:$C$10000)))))


Which is based on another formula in which I counted rows based on many criteria.

Neither of these formulas will count the rows which fall between two dates and have the status of Closed.


What am I doing wrong? Any help would be appreciated.

 

[NARAYANK991]

Hi ,


I tried replicating your problem , but your formula :


=SUMPRODUCT(('2011'!$B$2:$B$10000>=A101)*('2011'!$B$2:$B$10000<A102)*('2011'!$C$2:$C$10000="Closed"))


gives the correct answer. Can you post some data from your worksheet , say 10 rows , which satisfy the above conditions ?


Narayan

 

[Hui]

Phuzzy


I'd check that the cells in C2:C11 actually contain "Closed" and don't contain leading or trailing spaces

Check the cell length =Len(C2) it should be 6 for a cell with "Closed"

Retype one of the Closed word in C2:C11 does that change the answer ?


What was the source of the Data?

Data copied from Foreign Language Word/Excel or Websites may look like "Closed" but in fact be other characters

 

[Phuzzy]

Hanging my head in shame.....


Thanks for getting me to check the string of "Closed". There were no leading spaces, however I should have been looking for the string of "Close". Ugh.


I apologize for wasting your time, yet, am thankful for the help I received.