Asked by:
how to using sumproduct in an array
Question

On one worksheet I have a collection of dates tracking the component failures by multiple sites.
On another worksheet I have attempting to use the sumproduct function to count the number of failures in each month of the year by site. I am using the following function but don't know how to add the the site value.
=SUMPRODUCT(('H140nd'!$B$2:$B$738<>""),(YEAR('H140nd'!$B$2:$B$738)=$B$42)(MONTH('H140nd'!$B$2:$B$738)=3))
This give me the grand total for March, 2014. I need to break it down to individual sites:
year= 2014 VA CA FL Jan 10 4
4 2
Feb 100 25
75
25
March Apr May Jun Any help would be appreciated.
All replies

Let's say the sites are in column C.
=SUMPRODUCT(('H140nd'!$C$2:$C$738="VA"), (YEAR('H140nd'!$B$2:$B$738)=$B$42), (MONTH('H140nd'!$B$2:$B$738)=3))
If VA is in cell C42, you can use
=SUMPRODUCT(('H140nd'!$C$2:$C$738=C$42), (YEAR('H140nd'!$B$2:$B$738)=$B$42), (MONTH('H140nd'!$B$2:$B$738)=3))
and fill to the right.
You could enter the month numbers 1..12 in a column, say in K43:K54, and make the formula even more dynamic:
=SUMPRODUCT(('H140nd'!$C$2:$C$738=C$42), (YEAR('H140nd'!$B$2:$B$738)=$B$42), (MONTH('H140nd'!$B$2:$B$738)=$K43))
This can be filled both down and to the right.
Regards, Hans Vogelaar (http://www.eileenslounge.com)



Firstly, Thank you for you assistance.
I modified the variables as you suggested and the outputs are still '0'. The formula you provided is not working correctly, but I will be trying. I may try the countifs function to see if it will solve this.
Greatly appreciated.
dncaa

The formulas I posted are just guesswork, since I don't have all information.
If you wish, you can create a strippeddown copy of the workbook (without sensitive information) and make it available through one of the websites that let you upload and share a file, such as Microsoft OneDrive (https://onedrive.live.com), FileDropper (http://filedropper.com) or DropBox (http://www.dropbox.com). Then post a link to the uploaded and shared file here.
Regards, Hans Vogelaar (http://www.eileenslounge.com)
