sumproduct help

[madchemist]

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!

 

[Weaver]

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.

 

[barry houdini]

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]

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]

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]

"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]

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]

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]

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