Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
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! |
#3
|
|||
|
|||
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! |
#4
|
|||
|
|||
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! |
#5
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Updating links monthly... | Excel Worksheet Functions | |||
How do I sum YTD totals based on monthly totals | Excel Discussion (Misc queries) | |||
having trouble creating chart to show monthly totals | Charts and Charting in Excel | |||
How do I forecast monthly and annual totals from previous year's . | Excel Worksheet Functions | |||
Displaying YTD totals as you populate monthly information | Excel Worksheet Functions |