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!
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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:
Upvote 0
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))
 
Upvote 0
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!
 
Upvote 0
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....
 
Upvote 0
"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...
 
Upvote 0
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....
 
Upvote 0
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...
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,240
Members
448,555
Latest member
RobertJones1986

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
Back
Top