none
how to using sumproduct in an array RRS feed

  • 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(--('H-140nd'!$B$2:$B$738<>""),--(YEAR('H-140nd'!$B$2:$B$738)=$B$42)--(MONTH('H-140nd'!$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.

    Wednesday, July 9, 2014 7:40 PM

All replies

  • Let's say the sites are in column C.

    =SUMPRODUCT(--('H-140nd'!$C$2:$C$738="VA"), --(YEAR('H-140nd'!$B$2:$B$738)=$B$42), --(MONTH('H-140nd'!$B$2:$B$738)=3))

    If VA is in cell C42, you can use

    =SUMPRODUCT(--('H-140nd'!$C$2:$C$738=C$42), --(YEAR('H-140nd'!$B$2:$B$738)=$B$42), --(MONTH('H-140nd'!$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(--('H-140nd'!$C$2:$C$738=C$42), --(YEAR('H-140nd'!$B$2:$B$738)=$B$42), --(MONTH('H-140nd'!$B$2:$B$738)=$K43))

    This can be filled both down and to the right. 


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, July 9, 2014 7:50 PM
  • If there are blanks in any of the columns, would the function not work properly?

    Wednesday, July 9, 2014 8:55 PM
  • I don't think that matters. For example, if the date column is blank, the year cannot be equal to that in B42, and if the site column is blank, it isn't equal to any of the sites in C42 etc.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, July 9, 2014 9:12 PM
  • 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

    Wednesday, July 9, 2014 9:21 PM
  • The formulas I posted are just guesswork, since I don't have all information.

    If you wish, you can create a stripped-down 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)

    Wednesday, July 9, 2014 9:24 PM
  • thank you for this suggestion.  I will try this tomorrow.  if you are available to provide additional assistance, it would be greatly appreciated.

    dncaa

    Wednesday, July 9, 2014 9:33 PM