Need Formula to Reference/Match Value in Cell, not Formula in Cell

  • Hello,


    I have a formula that is comparing the value of one cell to another range of cells. The values could be the same (They should be displaying Month Year) but the formulas are different, and I think that's why my formula is resulting in zero.


    My formula is:
    =SUMPRODUCT(--('Current Projects'!$W$3:$AS$10=A7),'Current Projects'!$X$3:$AT$10)


    Where Range W3:AT contain alternating columns Date, Hours, Date, Hours, etc.
    The columns with the Date are derived from a formula (=TEXT(DATE(YEAR(S3),MONTH(S3)+1,DAY(NOW())),"MMMM YYYY")
    A7, which is the attempted comparison field, is just pulling from a list of possible dates (October 2013 through September 2014)


    Any idea how to make the formula reference/match the values instead of assuming they don't match because the formulas are different?

  • Re: Need Formula to Reference/Match Value in Cell, not Formula in Cell


    Instead of using sumproduct, have you tried using index/math instead?


    =index(range,row,column)
    for row and column...
    row=match(criteria-to-find,column-containg-match,0) 0 is for an exact match
    column=match(criteria-to-find,row-containg-match,0)

    Regards


    Ford

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!