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???

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???

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???

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???

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???

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???

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???

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???

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???

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???

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???