Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count in several colums
please help!!!
how can I set up a formula to count the frequency of a date from several colums and from 3 work sheets??? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count in several colums
Look at the COUNTIF function.
Dave -- Brevity is the soul of wit. "lee" wrote: please help!!! how can I set up a formula to count the frequency of a date from several colums and from 3 work sheets??? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count in several colums
I have looked at this but can not get my head aruond it!!!
I have an array of dates from "O1 to O18" & "P1 to P18" & "Q1 to Q18" This is then complicated by having 3 worksheets with this data on and I need to know how many times a particular date occours within all these ranges!! Can you help Dave?? "Dave F" wrote: Look at the COUNTIF function. Dave -- Brevity is the soul of wit. "lee" wrote: please help!!! how can I set up a formula to count the frequency of a date from several colums and from 3 work sheets??? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count in several colums
If the date is in C1 and the columns are A and B of sheets 1,2 and 3
try: =INDEX(FREQUENCY(Sheet1:Sheet3!A:B,C1-{1;0}),2) lee wrote: please help!!! how can I set up a formula to count the frequency of a date from several colums and from 3 work sheets??? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count in several colums
Enter the date you want to count in a helper cell, say A1.
=COUNTIF(O1:O18,A1)+COUNTIF(P1:P18,A1)+COUNTIF(Q1: Q18,A1) Dave -- Brevity is the soul of wit. "lee" wrote: I have looked at this but can not get my head aruond it!!! I have an array of dates from "O1 to O18" & "P1 to P18" & "Q1 to Q18" This is then complicated by having 3 worksheets with this data on and I need to know how many times a particular date occours within all these ranges!! Can you help Dave?? "Dave F" wrote: Look at the COUNTIF function. Dave -- Brevity is the soul of wit. "lee" wrote: please help!!! how can I set up a formula to count the frequency of a date from several colums and from 3 work sheets??? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count in several colums
Between both replys I think I have it cracked... Thank you very much.
"Lori" wrote: If the date is in C1 and the columns are A and B of sheets 1,2 and 3 try: =INDEX(FREQUENCY(Sheet1:Sheet3!A:B,C1-{1;0}),2) lee wrote: please help!!! how can I set up a formula to count the frequency of a date from several colums and from 3 work sheets??? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count in several colums
ok.... on my first workbook everything works perfect, however I need to set
up this formula to work for my other 2 woork books....??? this is my formula so far - =COUNTIF(O1:O616,AC10)+COUNTIF(P1:P616,AC10)+COUNT IF(Q1:Q616,AC10)+COUNTIF(R1:R616,AC10)+COUNTIF(S1: S616,AC10) thanks in advance. Lee. "Lori" wrote: If the date is in C1 and the columns are A and B of sheets 1,2 and 3 try: =INDEX(FREQUENCY(Sheet1:Sheet3!A:B,C1-{1;0}),2) lee wrote: please help!!! how can I set up a formula to count the frequency of a date from several colums and from 3 work sheets??? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count in several colums
I'm not sure what the question is if you say the formula works?
Are you asking how to reference an external workbook? If so, here's some information: http://ict.cas.psu.edu/training/howt..._maintain.html Dave -- Brevity is the soul of wit. "lee" wrote: ok.... on my first workbook everything works perfect, however I need to set up this formula to work for my other 2 woork books....??? this is my formula so far - =COUNTIF(O1:O616,AC10)+COUNTIF(P1:P616,AC10)+COUNT IF(Q1:Q616,AC10)+COUNTIF(R1:R616,AC10)+COUNTIF(S1: S616,AC10) thanks in advance. Lee. "Lori" wrote: If the date is in C1 and the columns are A and B of sheets 1,2 and 3 try: =INDEX(FREQUENCY(Sheet1:Sheet3!A:B,C1-{1;0}),2) lee wrote: please help!!! how can I set up a formula to count the frequency of a date from several colums and from 3 work sheets??? |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count in several colums
Hi Dave,
sorry....the formula given was for one worksheet in a folder, there are three worksheets with the same layout in this folder, I need to be able to use that formula to extract the data from all three worksheets..not just the one I have the formula in!! "lee" wrote: Between both replys I think I have it cracked... Thank you very much. "Lori" wrote: If the date is in C1 and the columns are A and B of sheets 1,2 and 3 try: =INDEX(FREQUENCY(Sheet1:Sheet3!A:B,C1-{1;0}),2) lee wrote: please help!!! how can I set up a formula to count the frequency of a date from several colums and from 3 work sheets??? |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count in several colums
Formulas that reference external workbooks have the following general syntax:
=COUNTIF('[Dec 06 adjustment.xls]adjustment'!$E$38:$E$45,"100000") (Note in this case that the formula above doesn't count the occurrence of the same date but rather the number of values greater than 100,000. For your purposes, the only part of the above formula you need is the syntax showing (1) the external workbook name (in the above, that would be "Dec 06 adjustment.xls"), (2) the external workbook sheet (which is "adjustment"), and (3) the external workbook range within the sheet named in number 2, which range you want the COUNTIF function to count.) Also see the link I gave you in an earlier response. Dave -- Brevity is the soul of wit. "lee" wrote: Hi Dave, sorry....the formula given was for one worksheet in a folder, there are three worksheets with the same layout in this folder, I need to be able to use that formula to extract the data from all three worksheets..not just the one I have the formula in!! "lee" wrote: Between both replys I think I have it cracked... Thank you very much. "Lori" wrote: If the date is in C1 and the columns are A and B of sheets 1,2 and 3 try: =INDEX(FREQUENCY(Sheet1:Sheet3!A:B,C1-{1;0}),2) lee wrote: please help!!! how can I set up a formula to count the frequency of a date from several colums and from 3 work sheets??? |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count in several colums
First of all, you can shorten your formula to something like this:
=COUNTIF(O1:S616,AC10) NOW, if you place this formula in the *same* cell on each of your 3 sheets, say AC1, then you can use a formula such as this: =SUM(Sheet1:Sheet3!AC1) To total that AC1 cell from all 3 sheets. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "lee" wrote in message ... ok.... on my first workbook everything works perfect, however I need to set up this formula to work for my other 2 woork books....??? this is my formula so far - =COUNTIF(O1:O616,AC10)+COUNTIF(P1:P616,AC10)+COUNT IF(Q1:Q616,AC10)+COUNTIF(R1:R616,AC10)+COUNTIF(S1: S616,AC10) thanks in advance. Lee. "Lori" wrote: If the date is in C1 and the columns are A and B of sheets 1,2 and 3 try: =INDEX(FREQUENCY(Sheet1:Sheet3!A:B,C1-{1;0}),2) lee wrote: please help!!! how can I set up a formula to count the frequency of a date from several colums and from 3 work sheets??? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to count values in two columns | Excel Discussion (Misc queries) | |||
Need formula to count spinoffs | Excel Worksheet Functions | |||
Count Intervals of 2 Consecutive Values in same Row and Return Count across Row | Excel Worksheet Functions | |||
Count Intervals of 1 Numeric value in a Row and Return Count down Column | Excel Worksheet Functions | |||
Conversion | Excel Worksheet Functions |