COUNTIF

[observer]

Request for Help

Trying to count DATA in A:A
Condition is that the DATE that is later than 01-01-2009 in G:G
with this and lots of variations of the following formul:

=COUNTIF('Consolidated Sheet'!$A:$A,'Consolidated Sheet'!$G:$G>=("01
January 2009")1)


Toronto

 

[Sheeloo]

Enter the date 1/1/2009 in G1 of 'Consolidated Sheet' and use
=COUNTIF('Consolidated Sheet'!A:A,">"&'Consolidated Sheet'!$G$1)

 

[Luke M]

Why are you counting column A if your criteria is column G?

Have to convert the date to a serial. (1-1-09 = 39814 [can be found easily
in XL by typing a date, then formatting cell as number])
=COUNTIF('Consolidated Sheet'!$G:$G,">39814")

If you're actually needing some criteria in A, you can use COUNTIFS if you
hx XL 2007, or just use SUMPRODUCT like this
=SUMPRODUCT(('Consolidated Sheet'!A1:A65536="My Criteria")*('Consolidated
Sheet'!G1:G65536>39814))

 

[observer]

Thanks Sheeloo for prompt support.

However it counted the all records, not only specified and met the condition
but all those records that were in A:A column.

Do you have alternative suggestions.

 

[observer]

Trying to count DATA in A:A
Condition is that the DATE that is later than 01-01-2009 in G:G
with this and lots of variations of the following formul:

=COUNTIF('Consolidated Sheet'!$A:$A,'Consolidated Sheet'!$G:$G>=("01
January 2009")1)
may be this additional info would enable you to help me.

Ward Date 1 Date 2
1 2008-11-10 2009-11-10
1 2008-11-27 2009-11-27
1 2008-11-28 2009-11-28
1 2008-12-09 2009-12-09
2 2008-12-11 2008-12-11
2 2008-12-23 2008-12-23
3 2008-12-24 2008-12-24
4 2008-12-25 2008-12-25

The task is to count Ward No (1-4) how many times it occurs in the specified
date in Column Date 1 and Date 2

 

[Sheeloo]

Use
=COUNTIF('Consolidated Sheet'!G:G,">"&DATE(2009,1,1))

It has nothing to do with values in Col A...
Will give you the count in G where date is > 1/1/2009

 

[observer]

Thanks for prompt help.

Sorry, it should be Column B in the posted data instead of G:.

However, I have to count how many times the specified ward in Column A
occurs in specified time that is recorded in Column B.

regards,
Toronto

Why are you counting column A if your criteria is column G?

Have to convert the date to a serial. (1-1-09 = 39814 [can be found easily
in XL by typing a date, then formatting cell as number])
=COUNTIF('Consolidated Sheet'!$G:$G,">39814")

If you're actually needing some criteria in A, you can use COUNTIFS if you
hx XL 2007, or just use SUMPRODUCT like this
=SUMPRODUCT(('Consolidated Sheet'!A1:A65536="My Criteria")*('Consolidated
Sheet'!G1:G65536>39814))

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

Request for Help

Trying to count DATA in A:A
Condition is that the DATE that is later than 01-01-2009 in G:G
with this and lots of variations of the following formul:

=COUNTIF('Consolidated Sheet'!$A:$A,'Consolidated Sheet'!$G:$G>=("01
January 2009")1)


Toronto

 

[observer]

Thanks for your kind efforts anyway.

It has to count value in Column A in a specified time that is recorded in
Column B.

regards,

 

[Sheeloo]

Try
=SUMPRODUCT(--(A1:A100=1),--(B1:B100>DATE(2009,1,1)))

It will you the count of dates in Col B greater than 1/1/2009 with 1 in Col
A in the same row...

 

[observer]

Sorry. It did not work.
--
Toronto

Try
=SUMPRODUCT(--(A1:A100=1),--(B1:B100>DATE(2009,1,1)))

It will you the count of dates in Col B greater than 1/1/2009 with 1 in Col
A in the same row...

 

[Sheeloo]

Col B has dates > 1/1/2009? Is it a date column or it is formatted as TEXT?

Format it as date...

enter 1/2/2009 in a couple of cells and see...

Download the sample.xls from http://wikisend.com/download/496756/Sample.xls

where I have used the formula
Cell with FORMULA =SUMPRODUCT(--(A1:A100=1),--(C1:C100>DATE(2009,1,1)))
Counting dates > 1/1/2009 in COL C