# sumproduct works in does not work with cell value

• ### 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

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 Thursday, November 13, 2014 5:37 PM
• Marked as answer by 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 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

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 Thursday, November 13, 2014 5:37 PM
• Marked as answer by 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 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