# sumproduct help

##### Board Regular
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!

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

#### Weaver

##### Well-known Member
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
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))

##### Board Regular
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!

##### Board Regular

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....

##### MrExcel MVP
"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...

##### Board Regular

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....

##### MrExcel MVP
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...

##### Board Regular
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

Replies
5
Views
228
Replies
3
Views
165
Replies
3
Views
103
Replies
1
Views
196
Replies
3
Views
252

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,903
Messages
5,834,309
Members
430,276
Latest member
legalcriminal015

### 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.

### Which adblocker are you using?

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

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