| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF
Enter the date 1/1/2009 in G1 of 'Consolidated Sheet' and use
=COUNTIF('Consolidated Sheet'!A:A,""&'Consolidated Sheet'!$G$1) "observer" wrote: 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF
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:G6553639814)) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "observer" wrote: 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF
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. -- Toronto "Sheeloo" wrote: Enter the date 1/1/2009 in G1 of 'Consolidated Sheet' and use =COUNTIF('Consolidated Sheet'!A:A,""&'Consolidated Sheet'!$G$1) "observer" wrote: 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF
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 -- Toronto "Sheeloo" wrote: Enter the date 1/1/2009 in G1 of 'Consolidated Sheet' and use =COUNTIF('Consolidated Sheet'!A:A,""&'Consolidated Sheet'!$G$1) "observer" wrote: 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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF
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" wrote: 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. -- Toronto "Sheeloo" wrote: Enter the date 1/1/2009 in G1 of 'Consolidated Sheet' and use =COUNTIF('Consolidated Sheet'!A:A,""&'Consolidated Sheet'!$G$1) "observer" wrote: 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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF
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 "Luke M" wrote: 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:G6553639814)) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "observer" wrote: 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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF
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, -- Toronto "Sheeloo" wrote: 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" wrote: 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. -- Toronto "Sheeloo" wrote: Enter the date 1/1/2009 in G1 of 'Consolidated Sheet' and use =COUNTIF('Consolidated Sheet'!A:A,""&'Consolidated Sheet'!$G$1) "observer" wrote: 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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF
Try
=SUMPRODUCT(--(A1:A100=1),--(B1:B100DATE(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" wrote: 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 -- Toronto "Sheeloo" wrote: Enter the date 1/1/2009 in G1 of 'Consolidated Sheet' and use =COUNTIF('Consolidated Sheet'!A:A,""&'Consolidated Sheet'!$G$1) "observer" wrote: 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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF
Sorry. It did not work.
-- Toronto "Sheeloo" wrote: Try =SUMPRODUCT(--(A1:A100=1),--(B1:B100DATE(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" wrote: 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 -- Toronto "Sheeloo" wrote: Enter the date 1/1/2009 in G1 of 'Consolidated Sheet' and use =COUNTIF('Consolidated Sheet'!A:A,""&'Consolidated Sheet'!$G$1) "observer" wrote: 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 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF
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:C100DATE(2009,1,1))) Counting dates 1/1/2009 in COL C "observer" wrote: Sorry. It did not work. -- Toronto "Sheeloo" wrote: Try =SUMPRODUCT(--(A1:A100=1),--(B1:B100DATE(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" wrote: 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 -- Toronto "Sheeloo" wrote: Enter the date 1/1/2009 in G1 of 'Consolidated Sheet' and use =COUNTIF('Consolidated Sheet'!A:A,""&'Consolidated Sheet'!$G$1) "observer" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| COUNTIF | Excel Discussion (Misc queries) | |||
| How do I use a countif function according to two other countif fu. | Excel Worksheet Functions | |||
| edit this =COUNTIF(A1:F16,"*1-2*")+COUNTIF(A1:F16,"*2-1*") | Excel Discussion (Misc queries) | |||
| COUNTIF or not to COUNTIF on a range in another sheet | Excel Worksheet Functions | |||
| COUNTIF in one colum then COUNTIF in another...??? | Excel Worksheet Functions | |||
![observer[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
