Monthly Totals
 

I am using the Sumproduct command to count the number of times a person's
name shows up in my spreadsheet. It looks like this: SUMPRODUCT(('[Murray
Division Pipeline.xls]Closed'!$B$1:$B$5000="Maxwell R")*1).

I need to add an additional criteria to look for a specific month. In column
W there is a date field that I want to have it pull from. So I would want to
count all records that have Maxwell R in column B and are for the month of
October in column W. Is there a way to do this with the Sumproduct function?
Thanks for the help!

Monthly Totals
 

One way

=SUMPRODUCT(--('C:\My Documents\[Murray Division
Pipeline.xls]Closed'!$B$1:$B$5000="Maxwell R"),--(MONTH('C:\My
Documents\[Murray Division Pipeline.xls]Closed'!$W$1:$W$5000)=10))


--

Regards,

Peo Sjoblom

"Jasmine" wrote in message
...
I am using the Sumproduct command to count the number of times a person's
name shows up in my spreadsheet. It looks like this: SUMPRODUCT(('[Murray
Division Pipeline.xls]Closed'!$B$1:$B$5000="Maxwell R")*1).

I need to add an additional criteria to look for a specific month. In
column
W there is a date field that I want to have it pull from. So I would want
to
count all records that have Maxwell R in column B and are for the month of
October in column W. Is there a way to do this with the Sumproduct
function?
Thanks for the help!

Monthly Totals
 

I tried this, but keep getting a #VALUE error.

"Peo Sjoblom" wrote:

One way

=SUMPRODUCT(--('C:\My Documents\[Murray Division
Pipeline.xls]Closed'!$B$1:$B$5000="Maxwell R"),--(MONTH('C:\My
Documents\[Murray Division Pipeline.xls]Closed'!$W$1:$W$5000)=10))


--

Regards,

Peo Sjoblom

"Jasmine" wrote in message
...
I am using the Sumproduct command to count the number of times a person's
name shows up in my spreadsheet. It looks like this: SUMPRODUCT(('[Murray
Division Pipeline.xls]Closed'!$B$1:$B$5000="Maxwell R")*1).

I need to add an additional criteria to look for a specific month. In
column
W there is a date field that I want to have it pull from. So I would want
to
count all records that have Maxwell R in column B and are for the month of
October in column W. Is there a way to do this with the Sumproduct
function?
Thanks for the help!

Monthly Totals
 

My fault, I gave you a formula with a path

=SUMPRODUCT(--(Closed!$B$1:$B$5000="Maxwell
R"),--(MONTH(Closed!$W$1:$W$5000)=10))

if that gives you value error then you must have text in column W

=SUMPRODUCT(--(Closed!$B$1:$B$5000="Maxwell
R"),--(Closed!$W$1:$W$5000="October"))

If you have month names, post back. Note that if you have numerical dates
like 10/14/05 in W and still get the error, that means they might have
trailing or leading spaces or other text characters
--
Regards,

Peo Sjoblom

(No private emails please)


"Jasmine" wrote in message
...
I tried this, but keep getting a #VALUE error.

"Peo Sjoblom" wrote:

One way

=SUMPRODUCT(--('C:\My Documents\[Murray Division
Pipeline.xls]Closed'!$B$1:$B$5000="Maxwell R"),--(MONTH('C:\My
Documents\[Murray Division Pipeline.xls]Closed'!$W$1:$W$5000)=10))


--

Regards,

Peo Sjoblom

"Jasmine" wrote in message
...
I am using the Sumproduct command to count the number of times a
person's
name shows up in my spreadsheet. It looks like this:
SUMPRODUCT(('[Murray
Division Pipeline.xls]Closed'!$B$1:$B$5000="Maxwell R")*1).

I need to add an additional criteria to look for a specific month. In
column
W there is a date field that I want to have it pull from. So I would
want
to
count all records that have Maxwell R in column B and are for the month
of
October in column W. Is there a way to do this with the Sumproduct
function?
Thanks for the help!

Monthly Totals
 

I think my problem is that some of the cells are blank. When I apply the
formula to a range that all has dates in it, it works fine. Is there anyway
to get around the blank cell? Thanks!

"Peo Sjoblom" wrote:

My fault, I gave you a formula with a path

=SUMPRODUCT(--(Closed!$B$1:$B$5000="Maxwell
R"),--(MONTH(Closed!$W$1:$W$5000)=10))

if that gives you value error then you must have text in column W

=SUMPRODUCT(--(Closed!$B$1:$B$5000="Maxwell
R"),--(Closed!$W$1:$W$5000="October"))

If you have month names, post back. Note that if you have numerical dates
like 10/14/05 in W and still get the error, that means they might have
trailing or leading spaces or other text characters
--
Regards,

Peo Sjoblom

(No private emails please)


"Jasmine" wrote in message
...
I tried this, but keep getting a #VALUE error.

"Peo Sjoblom" wrote:

One way

=SUMPRODUCT(--('C:\My Documents\[Murray Division
Pipeline.xls]Closed'!$B$1:$B$5000="Maxwell R"),--(MONTH('C:\My
Documents\[Murray Division Pipeline.xls]Closed'!$W$1:$W$5000)=10))


--

Regards,

Peo Sjoblom

"Jasmine" wrote in message
...
I am using the Sumproduct command to count the number of times a
person's
name shows up in my spreadsheet. It looks like this:
SUMPRODUCT(('[Murray
Division Pipeline.xls]Closed'!$B$1:$B$5000="Maxwell R")*1).

I need to add an additional criteria to look for a specific month. In
column
W there is a date field that I want to have it pull from. So I would
want
to
count all records that have Maxwell R in column B and are for the month
of
October in column W. Is there a way to do this with the Sumproduct
function?
Thanks for the help!