sumproduct help

madchemist

Board Regular
Joined
Jul 10, 2006
Messages
198
I have a spreadsheet that has a list of names in column B. In column D are a series of dates.

I want to count the number of times a name in column B has a date with a range of 1/1/09 to 1/31/09.

I tried using a sumproduct, but cannot seem to figure it out.

{=SUMPRODUCT(--(Sheet.xls'!$B:$B="Name"),--(Sheet.xls'!$D:$D=DATE(2009,1,1))))}

Can the sumproduct give me results with a range of dates?

Any help would be greatly appreciated. Thanks!
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Weaver

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
Are you on XL2007?

If not, you can't use whole columns in a sumproduct

Also, unless I'm very much mistaken, I don't believe it should be entered as a CSE (array) formula.

Also, you're referring to a xl file and there's no mention of a worksheet.
 
Last edited:

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
If you want the whole year then try a formula like

=SUMPRODUCT(--($B1:$B100="Name"),--(YEAR($D1:$D100)=2009))

otherwise, for any date range, where you have the dates in D2 and D3 you can use

=SUMPRODUCT(--($B1:$B100="Name"),--($D1:$D100>=D2),--($D1:$D100<=D3))
 

madchemist

Board Regular
Joined
Jul 10, 2006
Messages
198
I am using excel 2003.

hmmmm this seems to work:

=SUMPRODUCT(--($B$2:$B$432=$B$2),--(MONTH($D$2:$D$432)=11),--(YEAR($D$2:$D$432)=2007))

Is there anyway I can specify a name instead of $b$2? I tried = "Name" and that doesnt seem to work.

Thanks!
 

madchemist

Board Regular
Joined
Jul 10, 2006
Messages
198

ADVERTISEMENT

OK, i figured out how to do the name. The only problem I have is the range limitations. There arent always going to be the same number of rows in each column. Is there a way to write a formula to look for these three things across all cells in column b and d? Because if i change the range to include blank cells, the formula no longer works....
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
"Is there anyway I can specify a name instead of $b$2? I tried = "Name" and that doesnt seem to work."

The formula is well formed, so if the check for "Name" returns nothing, you have no "Name"s in your data - if you think otherwise, check the data for extra spaces etc...
 

madchemist

Board Regular
Joined
Jul 10, 2006
Messages
198

ADVERTISEMENT

Thanks, i had an error in the name, so thats why that wasnt working....

The other problem I have is the range limitations. There arent always going to be the same number of rows in each column. Is there a way to write a formula to look for these three things across all cells in column b and d? Because if i change the range to include blank cells, the formula no longer works....
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
You could use a dynamic named range that expands / contracts with your data, but in general just using a range larger than your data is likely to be should be fine...
 

madchemist

Board Regular
Joined
Jul 10, 2006
Messages
198
Thats what I thought as well and have done so in the past. For some reason when I use a range that includes blank cells, the formula returns 0.....Could it be because I am referencing cells on another workbook or something related to that?

=SUMPRODUCT(--('workbook.xls'!$B$2:$B$432="Name"),--(MONTH('workbook.xls'!$D$2:$D$432)=11),--(YEAR('workbook.xls'!$D$2:$D$432)=2007))

returns 6

=SUMPRODUCT(--('workbook.xls'!$B$2:$B$4000="Name"),--(MONTH('workbook.xls'!$D$2:$D$4000)=11),--(YEAR('workbook.xls'!$D$2:$D$4000)=2007))

returns 0, which is not right.

This is weird
 

Watch MrExcel Video

Forum statistics

Threads
1,122,193
Messages
5,594,774
Members
413,934
Latest member
austinb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top