+ Reply to Thread
Results 1 to 2 of 2

Sumproduct, Sumif, or Array Formula?

  1. #1
    Registered User
    Join Date
    05-02-2006
    Posts
    95

    Sumproduct, Sumif, or Array Formula?

    Hello Experts,
    I really need your help with this one. How do you modify this formula to add this first part?

    If each cell within range($B$2:$B$13)<>"", match the year and month of cell D$1, then sum the data range($C$2:$C$13),

    if each cell within range($B$2:$B$13) is blank, then choose range($A$2:$A$13) to match to the year and month of cell D$1, then sum the data range($C$2:$C$13). [The following formula somewhat does this part except that it doesn't have the ability to toggle between column A or B in each row]

    =SUMPRODUCT((YEAR($A$2:$A$13)=YEAR(D$1))*(MONTH($A$2:$A$13)=MONTH(D$1))*($C$2:$C$13))

    I've tried this array formula but it doesn't work.
    {=SUMPRODUCT(IF(YEAR($B$2:$B$13)<>"",(YEAR($B$2:$B$13)=YEAR(D$1)),(YEAR($A$2:$A$13)=YEAR(D$1)))*IF(MONTH($B$2:$B13)<>"",(MONTH($B$2:$B$13)=MONTH(D$1)),(MONTH($A$2:$A$13)=MONTH(D$1)))*($C$2:$C$13))}

    Please help.

    Thanks in advance,
    Ricky
    Last edited by ExcelQuestion; 05-24-2007 at 05:36 PM.

  2. #2
    Registered User
    Join Date
    05-02-2006
    Posts
    95
    Figured out a solution. Out of curiousity, is there a more condensed formula version than this one?

    =SUMPRODUCT((YEAR($A$2:$A$13)=YEAR(D$1))*(MONTH($A2:$A$13)=MONTH(D$1))*($B$2:$B$13="")*($C$2:$C$13))
    +SUMPRODUCT((YEAR($B$2:$B$13)=YEAR(D$1))*(MONTH($B$2:$B$13)=MONTH(D$1))*($C$2:$C$13))

    Thanks,
    Ricky

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1