Percentage of the occurance of a word in a date range
 

I have a spreadsheet with dates in Column A, and either the word "Res" or
"Comm" in Column M. How can I find out the percentage of "Res" for all the
records in January?

PS - there is an abundance of extreme talent in this community! Thanks for
all your postings!

Percentage of the occurance of a word in a date range
 

Assuming there are no empty cells in the date range (empty cells will
evaluate as month January).

=SUMPRODUCT(--(MONTH(A1:A20)=1),--(M1:M20="res"))/SUMPRODUCT(--(MONTH(A1:A20)=1),--(M1:M20<""))

Format as PERCENTAGE

--
Biff
Microsoft Excel MVP


"Art-SNL" wrote in message
...
I have a spreadsheet with dates in Column A, and either the word "Res" or
"Comm" in Column M. How can I find out the percentage of "Res" for all
the
records in January?

PS - there is an abundance of extreme talent in this community! Thanks
for
all your postings!

Percentage of the occurance of a word in a date range
 

Valko,

Thanks for the reply. I'm still having trouble (probably because I don't
have a good grasp of arrays). I tweaked it a little because I am referencing
a different worksheet named "Life Cycle". My data starts at row 10 and I
need to caluclate all future entries, so I adjusted the range. However my
data currently only has 150 rows. Any additional tips? Jeez, I'm dumb!

=SUMPRODUCT(--(MONTH('Life Cycle'!A10:A900)=1),--('Life
Cycle'!M10:M900="Res"))/SUMPRODUCT(--(MONTH('Life
Cycle'!A10:A900)=1),--('Life Cycle'!M10:M900<""))

"T. Valko" wrote:

Assuming there are no empty cells in the date range (empty cells will
evaluate as month January).

=SUMPRODUCT(--(MONTH(A1:A20)=1),--(M1:M20="res"))/SUMPRODUCT(--(MONTH(A1:A20)=1),--(M1:M20<""))

Format as PERCENTAGE

--
Biff
Microsoft Excel MVP


"Art-SNL" wrote in message
...
I have a spreadsheet with dates in Column A, and either the word "Res" or
"Comm" in Column M. How can I find out the percentage of "Res" for all
the
records in January?

PS - there is an abundance of extreme talent in this community! Thanks
for
all your postings!

Percentage of the occurance of a word in a date range
 

Valko,

I was right in my last post - I'm dumb. I found one invalid entry. User
error, your function worked great!

Thanks so much!

"Art-SNL" wrote:

Valko,

Thanks for the reply. I'm still having trouble (probably because I don't
have a good grasp of arrays). I tweaked it a little because I am referencing
a different worksheet named "Life Cycle". My data starts at row 10 and I
need to caluclate all future entries, so I adjusted the range. However my
data currently only has 150 rows. Any additional tips? Jeez, I'm dumb!

=SUMPRODUCT(--(MONTH('Life Cycle'!A10:A900)=1),--('Life
Cycle'!M10:M900="Res"))/SUMPRODUCT(--(MONTH('Life
Cycle'!A10:A900)=1),--('Life Cycle'!M10:M900<""))

"T. Valko" wrote:

Assuming there are no empty cells in the date range (empty cells will
evaluate as month January).

=SUMPRODUCT(--(MONTH(A1:A20)=1),--(M1:M20="res"))/SUMPRODUCT(--(MONTH(A1:A20)=1),--(M1:M20<""))

Format as PERCENTAGE

--
Biff
Microsoft Excel MVP


"Art-SNL" wrote in message
...
I have a spreadsheet with dates in Column A, and either the word "Res" or
"Comm" in Column M. How can I find out the percentage of "Res" for all
the
records in January?

PS - there is an abundance of extreme talent in this community! Thanks
for
all your postings!

Percentage of the occurance of a word in a date range
 

I'm still having trouble

The formula looks OK, what result do you get?

--
Biff
Microsoft Excel MVP


"Art-SNL" wrote in message
...
Valko,

Thanks for the reply. I'm still having trouble (probably because I don't
have a good grasp of arrays). I tweaked it a little because I am
referencing
a different worksheet named "Life Cycle". My data starts at row 10 and I
need to caluclate all future entries, so I adjusted the range. However my
data currently only has 150 rows. Any additional tips? Jeez, I'm dumb!

=SUMPRODUCT(--(MONTH('Life Cycle'!A10:A900)=1),--('Life
Cycle'!M10:M900="Res"))/SUMPRODUCT(--(MONTH('Life
Cycle'!A10:A900)=1),--('Life Cycle'!M10:M900<""))

"T. Valko" wrote:

Assuming there are no empty cells in the date range (empty cells will
evaluate as month January).

=SUMPRODUCT(--(MONTH(A1:A20)=1),--(M1:M20="res"))/SUMPRODUCT(--(MONTH(A1:A20)=1),--(M1:M20<""))

Format as PERCENTAGE

--
Biff
Microsoft Excel MVP


"Art-SNL" wrote in message
...
I have a spreadsheet with dates in Column A, and either the word "Res"
or
"Comm" in Column M. How can I find out the percentage of "Res" for all
the
records in January?

PS - there is an abundance of extreme talent in this community! Thanks
for
all your postings!

Percentage of the occurance of a word in a date range
 

The formula is great. I forgot to mention that some of the data is from last
year. How can I tweak this formula to only show the "Res" for January of
2008 (excluding 2007)?

Thanks,
Art

"T. Valko" wrote:

I'm still having trouble

The formula looks OK, what result do you get?

--
Biff
Microsoft Excel MVP


"Art-SNL" wrote in message
...
Valko,

Thanks for the reply. I'm still having trouble (probably because I don't
have a good grasp of arrays). I tweaked it a little because I am
referencing
a different worksheet named "Life Cycle". My data starts at row 10 and I
need to caluclate all future entries, so I adjusted the range. However my
data currently only has 150 rows. Any additional tips? Jeez, I'm dumb!

=SUMPRODUCT(--(MONTH('Life Cycle'!A10:A900)=1),--('Life
Cycle'!M10:M900="Res"))/SUMPRODUCT(--(MONTH('Life
Cycle'!A10:A900)=1),--('Life Cycle'!M10:M900<""))

"T. Valko" wrote:

Assuming there are no empty cells in the date range (empty cells will
evaluate as month January).

=SUMPRODUCT(--(MONTH(A1:A20)=1),--(M1:M20="res"))/SUMPRODUCT(--(MONTH(A1:A20)=1),--(M1:M20<""))

Format as PERCENTAGE

--
Biff
Microsoft Excel MVP


"Art-SNL" wrote in message
...
I have a spreadsheet with dates in Column A, and either the word "Res"
or
"Comm" in Column M. How can I find out the percentage of "Res" for all
the
records in January?

PS - there is an abundance of extreme talent in this community! Thanks
for
all your postings!

Percentage of the occurance of a word in a date range
 

Add an array to *each* SUMPRODUCT function like this:

--(YEAR('Life Cycle'!A10:A900)=2008)

Since you're testing for a specific year you don't have to be concerned
about empty cells evaluating as month January.

--
Biff
Microsoft Excel MVP


"Art-SNL" wrote in message
...
The formula is great. I forgot to mention that some of the data is from
last
year. How can I tweak this formula to only show the "Res" for January of
2008 (excluding 2007)?

Thanks,
Art

"T. Valko" wrote:

I'm still having trouble

The formula looks OK, what result do you get?

--
Biff
Microsoft Excel MVP


"Art-SNL" wrote in message
...
Valko,

Thanks for the reply. I'm still having trouble (probably because I
don't
have a good grasp of arrays). I tweaked it a little because I am
referencing
a different worksheet named "Life Cycle". My data starts at row 10 and
I
need to caluclate all future entries, so I adjusted the range. However
my
data currently only has 150 rows. Any additional tips? Jeez, I'm
dumb!

=SUMPRODUCT(--(MONTH('Life Cycle'!A10:A900)=1),--('Life
Cycle'!M10:M900="Res"))/SUMPRODUCT(--(MONTH('Life
Cycle'!A10:A900)=1),--('Life Cycle'!M10:M900<""))

"T. Valko" wrote:

Assuming there are no empty cells in the date range (empty cells will
evaluate as month January).

=SUMPRODUCT(--(MONTH(A1:A20)=1),--(M1:M20="res"))/SUMPRODUCT(--(MONTH(A1:A20)=1),--(M1:M20<""))

Format as PERCENTAGE

--
Biff
Microsoft Excel MVP


"Art-SNL" wrote in message
...
I have a spreadsheet with dates in Column A, and either the word
"Res"
or
"Comm" in Column M. How can I find out the percentage of "Res" for
all
the
records in January?

PS - there is an abundance of extreme talent in this community!
Thanks
for
all your postings!

Percentage of the occurance of a word in a date range
 

try =countif(A10:A900,"res") that will give you the number of times "res"
occurs, then divide it by the number of cells and display it as a percentage

[RLK] Rollin' Like Kingz


"T. Valko" wrote:

Add an array to *each* SUMPRODUCT function like this:

--(YEAR('Life Cycle'!A10:A900)=2008)

Since you're testing for a specific year you don't have to be concerned
about empty cells evaluating as month January.

--
Biff
Microsoft Excel MVP


"Art-SNL" wrote in message
...
The formula is great. I forgot to mention that some of the data is from
last
year. How can I tweak this formula to only show the "Res" for January of
2008 (excluding 2007)?

Thanks,
Art

"T. Valko" wrote:

I'm still having trouble

The formula looks OK, what result do you get?

--
Biff
Microsoft Excel MVP


"Art-SNL" wrote in message
...
Valko,

Thanks for the reply. I'm still having trouble (probably because I
don't
have a good grasp of arrays). I tweaked it a little because I am
referencing
a different worksheet named "Life Cycle". My data starts at row 10 and
I
need to caluclate all future entries, so I adjusted the range. However
my
data currently only has 150 rows. Any additional tips? Jeez, I'm
dumb!

=SUMPRODUCT(--(MONTH('Life Cycle'!A10:A900)=1),--('Life
Cycle'!M10:M900="Res"))/SUMPRODUCT(--(MONTH('Life
Cycle'!A10:A900)=1),--('Life Cycle'!M10:M900<""))

"T. Valko" wrote:

Assuming there are no empty cells in the date range (empty cells will
evaluate as month January).

=SUMPRODUCT(--(MONTH(A1:A20)=1),--(M1:M20="res"))/SUMPRODUCT(--(MONTH(A1:A20)=1),--(M1:M20<""))

Format as PERCENTAGE

--
Biff
Microsoft Excel MVP


"Art-SNL" wrote in message
...
I have a spreadsheet with dates in Column A, and either the word
"Res"
or
"Comm" in Column M. How can I find out the percentage of "Res" for
all
the
records in January?

PS - there is an abundance of extreme talent in this community!
Thanks
for
all your postings!

Percentage of the occurance of a word in a date range
 

I don't think that'll work.

You need to account for a specific time period.

--
Biff
Microsoft Excel MVP


"Alan" wrote in message
...
try =countif(A10:A900,"res") that will give you the number of times "res"
occurs, then divide it by the number of cells and display it as a
percentage

[RLK] Rollin' Like Kingz


"T. Valko" wrote:

Add an array to *each* SUMPRODUCT function like this:

--(YEAR('Life Cycle'!A10:A900)=2008)

Since you're testing for a specific year you don't have to be concerned
about empty cells evaluating as month January.

--
Biff
Microsoft Excel MVP


"Art-SNL" wrote in message
...
The formula is great. I forgot to mention that some of the data is
from
last
year. How can I tweak this formula to only show the "Res" for January
of
2008 (excluding 2007)?

Thanks,
Art

"T. Valko" wrote:

I'm still having trouble

The formula looks OK, what result do you get?

--
Biff
Microsoft Excel MVP


"Art-SNL" wrote in message
...
Valko,

Thanks for the reply. I'm still having trouble (probably because I
don't
have a good grasp of arrays). I tweaked it a little because I am
referencing
a different worksheet named "Life Cycle". My data starts at row 10
and
I
need to caluclate all future entries, so I adjusted the range.
However
my
data currently only has 150 rows. Any additional tips? Jeez, I'm
dumb!

=SUMPRODUCT(--(MONTH('Life Cycle'!A10:A900)=1),--('Life
Cycle'!M10:M900="Res"))/SUMPRODUCT(--(MONTH('Life
Cycle'!A10:A900)=1),--('Life Cycle'!M10:M900<""))

"T. Valko" wrote:

Assuming there are no empty cells in the date range (empty cells
will
evaluate as month January).

=SUMPRODUCT(--(MONTH(A1:A20)=1),--(M1:M20="res"))/SUMPRODUCT(--(MONTH(A1:A20)=1),--(M1:M20<""))

Format as PERCENTAGE

--
Biff
Microsoft Excel MVP


"Art-SNL" wrote in message
...
I have a spreadsheet with dates in Column A, and either the word
"Res"
or
"Comm" in Column M. How can I find out the percentage of "Res"
for
all
the
records in January?

PS - there is an abundance of extreme talent in this community!
Thanks
for
all your postings!