none
sumproduct works in does not work with cell value RRS feed

  • Question

  • Hi,

    I use "=SUMPRODUCT((SALARY!$G$10:$G$51>=C13)*(SALARY!$G$10:$G$51<=C13+2),SALARY!$Q$10:$Q$51)"

    formula for a range sum. When I use =SUMPRODUCT((SALARY!$G$10:$G$51>=C13)*(SALARY!$G$10:$G$51<=C13+A1),SALARY!$Q$10:$Q$51), Here SALARY!$Q$10:$Q$51 are number and SALARY!$G$10:$G$51>=C13 are dates. It gives zero, Although A1=2.

    Is it possible to get same results. I want, because if 2 needed to be modified in future.

    regards.

    dockhem

    Thursday, November 13, 2014 4:46 AM

Answers

  • Hi Bernie Deitrick,

    Thank you for your reply.

    This also not working. Mean while I tried this and it working. Please see if it is right thing or may create problem later on ? It is date range from first to last date of month.

    =SUMPRODUCT((SALARY!$G$10:$G$1026>=C10)*(SALARY!$G$10:$G$1026<=EOMONTH(C10,0)),SALARY!$Q$10:$Q$1026)

    regards,

    dockhem


    • Edited by dockhem2 Thursday, November 13, 2014 5:37 PM
    • Marked as answer by dockhem2 Friday, November 14, 2014 1:16 AM
    Thursday, November 13, 2014 5:36 PM
  • That will work, as long as you want entire months. You could also do the same with a pivot table, without using any formulas.
    • Marked as answer by dockhem2 Friday, November 14, 2014 1:16 AM
    Thursday, November 13, 2014 5:41 PM

All replies

  • Your formula worked fine for me, in any form.  Maybe try

    =SUMPRODUCT((SALARY!$G$10:$G$51>=C13)*(SALARY!$G$10:$G$51<=(C13+A1))*SALARY!$Q$10:$Q$51)

    since the addition may be taking place after the comparison.


    Thursday, November 13, 2014 5:11 PM
  • Hi Bernie Deitrick,

    Thank you for your reply.

    This also not working. Mean while I tried this and it working. Please see if it is right thing or may create problem later on ? It is date range from first to last date of month.

    =SUMPRODUCT((SALARY!$G$10:$G$1026>=C10)*(SALARY!$G$10:$G$1026<=EOMONTH(C10,0)),SALARY!$Q$10:$Q$1026)

    regards,

    dockhem


    • Edited by dockhem2 Thursday, November 13, 2014 5:37 PM
    • Marked as answer by dockhem2 Friday, November 14, 2014 1:16 AM
    Thursday, November 13, 2014 5:36 PM
  • That will work, as long as you want entire months. You could also do the same with a pivot table, without using any formulas.
    • Marked as answer by dockhem2 Friday, November 14, 2014 1:16 AM
    Thursday, November 13, 2014 5:41 PM
  • Thank you Bernie Deitrick,

    Actually I do not know anything about pivot table.

    regards

    dockhem

    Friday, November 14, 2014 1:15 AM