| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting using data from 2 columns
I need to count the number of rows which have a particular date in column A
and then a particular name in column J, I have tried countif formula but can not get it to work. Basically i need to count all the rows that contain the "Daily Telegraph" in column J as well as containing say "25 JUL" IN COLUMN A, any ideas? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting using data from 2 columns
Hi,
=COUNTIF(J1:J100,"Daily Telegraph")+COUNTIF(A1:A1000,"25 JUL") "darbs" wrote: I need to count the number of rows which have a particular date in column A and then a particular name in column J, I have tried countif formula but can not get it to work. Basically i need to count all the rows that contain the "Daily Telegraph" in column J as well as containing say "25 JUL" IN COLUMN A, any ideas? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting using data from 2 columns
Opps, too many "0" in the 2nd part of the formula, use
=COUNTIF(J1:J100,"Daily Telegraph")+COUNTIF(A1:A100,"25 JUL") "Eduardo" wrote: Hi, =COUNTIF(J1:J100,"Daily Telegraph")+COUNTIF(A1:A1000,"25 JUL") "darbs" wrote: I need to count the number of rows which have a particular date in column A and then a particular name in column J, I have tried countif formula but can not get it to work. Basically i need to count all the rows that contain the "Daily Telegraph" in column J as well as containing say "25 JUL" IN COLUMN A, any ideas? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting using data from 2 columns
Thank you. sorry, dont think I explained myself well - this works but not
correctly I have 24 rows that contain 25 Jul in column A and out of these 19 contain daily telegraph in column J and thats the number i am looking for but instead this formula is giving me 72 as it is adding 24 from column a to the 48(total number of rows in column J that contain daily telgraph) Hope this is clearer. Thanks "Eduardo" wrote: Opps, too many "0" in the 2nd part of the formula, use =COUNTIF(J1:J100,"Daily Telegraph")+COUNTIF(A1:A100,"25 JUL") "Eduardo" wrote: Hi, =COUNTIF(J1:J100,"Daily Telegraph")+COUNTIF(A1:A1000,"25 JUL") "darbs" wrote: I need to count the number of rows which have a particular date in column A and then a particular name in column J, I have tried countif formula but can not get it to work. Basically i need to count all the rows that contain the "Daily Telegraph" in column J as well as containing say "25 JUL" IN COLUMN A, any ideas? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting using data from 2 columns
Hi,
I misunderstood use =SUMPRODUCT((J1:J100="Daily Telegraph")+(A1:A100="25 JUL")) "darbs" wrote: Thank you. sorry, dont think I explained myself well - this works but not correctly I have 24 rows that contain 25 Jul in column A and out of these 19 contain daily telegraph in column J and thats the number i am looking for but instead this formula is giving me 72 as it is adding 24 from column a to the 48(total number of rows in column J that contain daily telgraph) Hope this is clearer. Thanks "Eduardo" wrote: Opps, too many "0" in the 2nd part of the formula, use =COUNTIF(J1:J100,"Daily Telegraph")+COUNTIF(A1:A100,"25 JUL") "Eduardo" wrote: Hi, =COUNTIF(J1:J100,"Daily Telegraph")+COUNTIF(A1:A1000,"25 JUL") "darbs" wrote: I need to count the number of rows which have a particular date in column A and then a particular name in column J, I have tried countif formula but can not get it to work. Basically i need to count all the rows that contain the "Daily Telegraph" in column J as well as containing say "25 JUL" IN COLUMN A, any ideas? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting using data from 2 columns
Opps, again same mistake use this formula
=SUMPRODUCT(--(J1:J100="Daily Telegraph"),--(A1:A100="25 JUL")) "Eduardo" wrote: Hi, I misunderstood use =SUMPRODUCT((J1:J100="Daily Telegraph")+(A1:A100="25 JUL")) "darbs" wrote: Thank you. sorry, dont think I explained myself well - this works but not correctly I have 24 rows that contain 25 Jul in column A and out of these 19 contain daily telegraph in column J and thats the number i am looking for but instead this formula is giving me 72 as it is adding 24 from column a to the 48(total number of rows in column J that contain daily telgraph) Hope this is clearer. Thanks "Eduardo" wrote: Opps, too many "0" in the 2nd part of the formula, use =COUNTIF(J1:J100,"Daily Telegraph")+COUNTIF(A1:A100,"25 JUL") "Eduardo" wrote: Hi, =COUNTIF(J1:J100,"Daily Telegraph")+COUNTIF(A1:A1000,"25 JUL") "darbs" wrote: I need to count the number of rows which have a particular date in column A and then a particular name in column J, I have tried countif formula but can not get it to work. Basically i need to count all the rows that contain the "Daily Telegraph" in column J as well as containing say "25 JUL" IN COLUMN A, any ideas? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting using data from 2 columns
Assuming your 25 Jul is TEXT
=sumproduct((a2:a22="25 July")*(j2:j22="daily telegraph")) -- Don Guillett Microsoft MVP Excel SalesAid Software "darbs" wrote in message ... I need to count the number of rows which have a particular date in column A and then a particular name in column J, I have tried countif formula but can not get it to work. Basically i need to count all the rows that contain the "Daily Telegraph" in column J as well as containing say "25 JUL" IN COLUMN A, any ideas? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting using data from 2 columns
Tried both of these and its just returning 0
"Don Guillett" wrote: Assuming your 25 Jul is TEXT =sumproduct((a2:a22="25 July")*(j2:j22="daily telegraph")) -- Don Guillett Microsoft MVP Excel SalesAid Software "darbs" wrote in message ... I need to count the number of rows which have a particular date in column A and then a particular name in column J, I have tried countif formula but can not get it to work. Basically i need to count all the rows that contain the "Daily Telegraph" in column J as well as containing say "25 JUL" IN COLUMN A, any ideas? |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting using data from 2 columns
Tried both of these and its just returning 0 "Eduardo" wrote: Opps, again same mistake use this formula =SUMPRODUCT(--(J1:J100="Daily Telegraph"),--(A1:A100="25 JUL")) "Eduardo" wrote: Hi, I misunderstood use =SUMPRODUCT((J1:J100="Daily Telegraph")+(A1:A100="25 JUL")) "darbs" wrote: Thank you. sorry, dont think I explained myself well - this works but not correctly I have 24 rows that contain 25 Jul in column A and out of these 19 contain daily telegraph in column J and thats the number i am looking for but instead this formula is giving me 72 as it is adding 24 from column a to the 48(total number of rows in column J that contain daily telgraph) Hope this is clearer. Thanks "Eduardo" wrote: Opps, too many "0" in the 2nd part of the formula, use =COUNTIF(J1:J100,"Daily Telegraph")+COUNTIF(A1:A100,"25 JUL") "Eduardo" wrote: Hi, =COUNTIF(J1:J100,"Daily Telegraph")+COUNTIF(A1:A1000,"25 JUL") "darbs" wrote: I need to count the number of rows which have a particular date in column A and then a particular name in column J, I have tried countif formula but can not get it to work. Basically i need to count all the rows that contain the "Daily Telegraph" in column J as well as containing say "25 JUL" IN COLUMN A, any ideas? |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting using data from 2 columns
Hi,
check if you don't have any blank space, the text in the formula has to be exactly the same as in the column "darbs" wrote: Tried both of these and its just returning 0 "Eduardo" wrote: Opps, again same mistake use this formula =SUMPRODUCT(--(J1:J100="Daily Telegraph"),--(A1:A100="25 JUL")) "Eduardo" wrote: Hi, I misunderstood use =SUMPRODUCT((J1:J100="Daily Telegraph")+(A1:A100="25 JUL")) "darbs" wrote: Thank you. sorry, dont think I explained myself well - this works but not correctly I have 24 rows that contain 25 Jul in column A and out of these 19 contain daily telegraph in column J and thats the number i am looking for but instead this formula is giving me 72 as it is adding 24 from column a to the 48(total number of rows in column J that contain daily telgraph) Hope this is clearer. Thanks "Eduardo" wrote: Opps, too many "0" in the 2nd part of the formula, use =COUNTIF(J1:J100,"Daily Telegraph")+COUNTIF(A1:A100,"25 JUL") "Eduardo" wrote: Hi, =COUNTIF(J1:J100,"Daily Telegraph")+COUNTIF(A1:A1000,"25 JUL") "darbs" wrote: I need to count the number of rows which have a particular date in column A and then a particular name in column J, I have tried countif formula but can not get it to work. Basically i need to count all the rows that contain the "Daily Telegraph" in column J as well as containing say "25 JUL" IN COLUMN A, any ideas? |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting using data from 2 columns
Hi,
if there is a blank space you can ride off it doing in another column =trim(A1) then copy this result in column A pasting as values "darbs" wrote: Tried both of these and its just returning 0 "Eduardo" wrote: Opps, again same mistake use this formula =SUMPRODUCT(--(J1:J100="Daily Telegraph"),--(A1:A100="25 JUL")) "Eduardo" wrote: Hi, I misunderstood use =SUMPRODUCT((J1:J100="Daily Telegraph")+(A1:A100="25 JUL")) "darbs" wrote: Thank you. sorry, dont think I explained myself well - this works but not correctly I have 24 rows that contain 25 Jul in column A and out of these 19 contain daily telegraph in column J and thats the number i am looking for but instead this formula is giving me 72 as it is adding 24 from column a to the 48(total number of rows in column J that contain daily telgraph) Hope this is clearer. Thanks "Eduardo" wrote: Opps, too many "0" in the 2nd part of the formula, use =COUNTIF(J1:J100,"Daily Telegraph")+COUNTIF(A1:A100,"25 JUL") "Eduardo" wrote: Hi, =COUNTIF(J1:J100,"Daily Telegraph")+COUNTIF(A1:A1000,"25 JUL") "darbs" wrote: I need to count the number of rows which have a particular date in column A and then a particular name in column J, I have tried countif formula but can not get it to work. Basically i need to count all the rows that contain the "Daily Telegraph" in column J as well as containing say "25 JUL" IN COLUMN A, any ideas? |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting using data from 2 columns
sorry it is a date
"Don Guillett" wrote: Is your 25 Jul really a date or TEXT??? You failed to tell us. -- Don Guillett Microsoft MVP Excel SalesAid Software "darbs" wrote in message ... Tried both of these and its just returning 0 "Don Guillett" wrote: Assuming your 25 Jul is TEXT =sumproduct((a2:a22="25 July")*(j2:j22="daily telegraph")) -- Don Guillett Microsoft MVP Excel SalesAid Software "darbs" wrote in message ... I need to count the number of rows which have a particular date in column A and then a particular name in column J, I have tried countif formula but can not get it to work. Basically i need to count all the rows that contain the "Daily Telegraph" in column J as well as containing say "25 JUL" IN COLUMN A, any ideas? |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting using data from 2 columns
Hi,
Try this =sumproduct((range1=date(2009,7,25))*(range2="Dail y Telegraph")) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "darbs" wrote in message ... sorry it is a date "Don Guillett" wrote: Is your 25 Jul really a date or TEXT??? You failed to tell us. -- Don Guillett Microsoft MVP Excel SalesAid Software "darbs" wrote in message ... Tried both of these and its just returning 0 "Don Guillett" wrote: Assuming your 25 Jul is TEXT =sumproduct((a2:a22="25 July")*(j2:j22="daily telegraph")) -- Don Guillett Microsoft MVP Excel SalesAid Software "darbs" wrote in message ... I need to count the number of rows which have a particular date in column A and then a particular name in column J, I have tried countif formula but can not get it to work. Basically i need to count all the rows that contain the "Daily Telegraph" in column J as well as containing say "25 JUL" IN COLUMN A, any ideas? |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting using data from 2 columns
Hi,
=SUMPRODUCT(--(J1:J100="Daily Telegraph"),--(A1:A100=date(2009,7,25))) "darbs" wrote: sorry it is a date "Don Guillett" wrote: Is your 25 Jul really a date or TEXT??? You failed to tell us. -- Don Guillett Microsoft MVP Excel SalesAid Software "darbs" wrote in message ... Tried both of these and its just returning 0 "Don Guillett" wrote: Assuming your 25 Jul is TEXT =sumproduct((a2:a22="25 July")*(j2:j22="daily telegraph")) -- Don Guillett Microsoft MVP Excel SalesAid Software "darbs" wrote in message ... I need to count the number of rows which have a particular date in column A and then a particular name in column J, I have tried countif formula but can not get it to work. Basically i need to count all the rows that contain the "Daily Telegraph" in column J as well as containing say "25 JUL" IN COLUMN A, any ideas? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Counting 2 different pieces of data in 2 different columns | Excel Worksheet Functions | |||
| counting data in multiple columns | Excel Discussion (Misc queries) | |||
| Counting Data in Columns | Excel Discussion (Misc queries) | |||
| Counting Data From 2 Different Columns | Excel Discussion (Misc queries) | |||
| Counting data in columns | Excel Discussion (Misc queries) | |||
Linear Mode
