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!

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" <[email protected]> wrote in message
news:[email protected]...
> 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!

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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!
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
>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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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!
>>
>>
>>

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" <[email protected]> wrote in message
> news:[email protected]...
> >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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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!
> >>
> >>
> >>
>
>