Count Cells in a Column within a date range.

[obi2bad]

From the posted data below, I would like to count the number of cells that contain the value or text "True" that fall within a date range, say 1st of June to End of June, and sum it in one cell. I have tried SUMPRODUCT and COUNTIF formulas, but I seem to be missing something.


Can soomeone help please?


A B

30/06/2011 True

30/06/2011 True

30/06/2011 True

30/06/2011 False

30/06/2011 True

28/06/2011 False

30/06/2011 False

29/06/2011 False

30/06/2011 False

29/07/2011 False

29/07/2011 False

06/07/2011 False

07/07/2011 False

04/07/2011 True

07/07/2011 True

12/07/2011 True

07/07/2011 False

06/07/2011 False

07/07/2011 False

07/07/2011 True

07/07/2011 True

15/07/2011 False

12/07/2011 False

15/07/2011 False

07/07/2011 False

07/07/2011 False

04/07/2011 True

07/07/2011 False

15/07/2011 True

 

[Luke M]

Let's assume your start/end dates are in D1 and D2, rsepctively.

=SUMPRODUCT(--(A1:A100>=D1),--(A1:A100<=D2),--(B1:B100))

 

[fred]

i'm sorry it is a bad sample data table. you have both


30/6/2011 True

30/6/2011 False

30/6/2011 True


Which one is true and which one is false??


also, you have 15/7/2011 as true but 07/07/2011 as false. I don't understand your criteria fitting into between 1/6/2011 and 30/6/2011


once fixed you can use countif(range,"True")


I guess when you use if then to filter you had if(test,"True","False"). Try removing the "" and just if(test,True,False) is sufficient.

 

[fred]

oh i see what you want, please ignore my last post.

 

[obi2bad]

Hi Fred, if u look carefully u will find that the count of "TRUE" in column B between 1st june and 30th june in column A is 4. I have tried the exact same sumproduct formula above to no avail.


Any Ideas somebody?

 

[obi2bad]

Hi Fred, if u look carefully u will find that the count of "TRUE" in column B between 1st june and 30th june in column A is 4. I have tried the exact same sumproduct formula above to no avail.


Any Ideas somebody?

 

[obi2bad]

 

[SirJB7]

Hi, obi2bad!

Just right, the famous and fabulous SUMPRODUCT doesn't to the work.

Look at http://www.2shared.com/file/WNkwUcER/Count_Cells_in_a_Column_within.html

In cell C1 the failure and in E1 I think that the success.

Regards!

 

[Hui]

Obi2bad

I tried Lukes formula and it returns 4 for me

So I am going to assume that, you have more data below or above what you have shown us


I'd suggest shortening the ranges to just include the data area to start with:

You gave us 29 rows of Data without Headers

So If your Data is in A1:B29 try:

=SUMPRODUCT((A1:A29>=D1)*(A1:A29<=D2)*(B1:B29))


alternatively does this work:

=COUNTIFS(A1:A29,">="&D1,A1:A29,"<="&D2,B1:B29,TRUE)


If this doesn't help can you post the full sheet of data or tell us what result your getting

 

[SirJB7]

Hi, obi2bad!

After reading Hui's post I doubted if I have adjusted the range from 100 to 29 in my uploaded file, I cheched it again, and it says 29. It's still doesn't working for me, it insists in returning zero.

Regards!

 

[Hui]

What version/language of Excel ?

Did Countifs help?

 

[Hui]

SirJB7

Your Sumproduct doesn't work in your upload file as it is incorrectly constructed in C1:

it should be

=SUMPRODUCT((A1:A29>=D$1)*(A1:A29<=D$2)*(B1:B29="True"))


Each logic Block, and there are 3, must have maths performed on it

that can be done like:

=SUMPRODUCT((A1:A29>=D$1)*(A1:A29<=D$2)*(B1:B29="True"))


or as Luke had

=SUMPRODUCT(--(A1:A100>=D1),--(A1:A100<=D2),--(B1:B100))

I prefer the former as its neater

 

[SirJB7]

@Hui

Hi!

Excel 2010 spanish Argentine.

In the uploaded example I used CONTAR.SI.CONJUNTO -----> in english COUNTIFS, and it works fine: returns the desired 4.

Regards!

 

[Hui]

SirJB7, obi2bad

I also think that Column B contains Text not the Boolean True/False

To convert to True/False select Column B

Goto Data tab, Text to Columns and just step through and take all the options

Notice that The True/False center justifies on finalising this step


So If Column B is text use:

=SUMPRODUCT((A1:A29>=D1)*(A1:A29<=D2)*(B1:B29="True"))


If Column B is Boolean True/False

=SUMPRODUCT((A1:A29>=D1)*(A1:A29<=D2)*(B1:B29))

 

[SirJB7]

@Hui

Hi again!

When I downloaded the file that obi2bad created, I've noticed that the values True and False in english were not traduced to my locale values of Verdadero and Falso, so I converted column B to text, and used both in the SUMPRODUCT and in the COUNTIFS those values quoted as text constants, just as you stated before.

I invite you to take a look to the uploaded file of post #7.

Regards!

 

[Hui]

@SirJB7

That is the file all my comments after that are based on

In the English version of Excel

I simply changed the two ,'s to *'s in Cell C1

From: =SUMPRODUCT((A1:A29>=D$1),(A1:A29<=D$2),(B1:B29="True"))

To: =SUMPRODUCT((A1:A29>=D$1)*(A1:A29<=D$2)*(B1:B29="True"))

which displays 4


The values in Column B are text and so the language doesn't matter


If you have changed the values in Column B to "Verdadero"

=SUMPRODUCT((A1:A29>=D$1)*(A1:A29<=D$2)*(B1:B29="Verdadero")) should work

if they are Boolean's

=SUMPRODUCT((A1:A29>=D$1)*(A1:A29<=D$2)*(B1:B29)) should work

 

[SirJB7]

@Hui (think we're invading and abusing from obi2bad post)

Re-uploaded now http://www.2shared.com/file/_Y2EUnqB/Count_Cells_in_a_Column_within.html

And you're right. With your formula, with '*' instead of ',' as in Luke M's formula, it works fine, as well as with my and your COUNTIFS.

Regards!