Hi All,
I do have a problem using the formula of Sumproduct when i need to find out a few product total amount and this formula just work great SUMPRODUCT(--(LEFT('Sheet2'!$D:$D,7)=$C$13),--(LEFT('Sheet2'!$E:$E,4)<>"Pen"),--(LEFT('Sheet2'!$E:$E,4)<>"Book"),'Sheet2'!$F:$F) but the problem arise when in C13 i wanted to sum few product code for example in C13 column =HT4568,HT8963 when i wanted to put such comma to sum up value in sheet2 then this formula had failed to perform so may i know is there any solution.
Problem SumProduct with Comma
-
HansV
- Administrator
- Posts: 78549
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Problem SumProduct with Comma
You could place each value in a separate cell, e.g. HT4568 in C13 and HT8963 in C14. Then add the SUMPRODUCT formulas for each cell.
Remarks:
1) HT4568 and HT8963 have length 6, not length 7, so you should use LEFT(...,6) instead of LEFT(...,7). Or use LEFT(...,LEN($C$13))
2) Pen has length 3, so LEFT('Sheet2'!$E:$E,4)<>"Pen" is always true. Use LEFT('Sheet2'!$E:$E,3), or LEFT('Sheet2'!$E:$E,LEN("Pen"))
Remarks:
1) HT4568 and HT8963 have length 6, not length 7, so you should use LEFT(...,6) instead of LEFT(...,7). Or use LEFT(...,LEN($C$13))
2) Pen has length 3, so LEFT('Sheet2'!$E:$E,4)<>"Pen" is always true. Use LEFT('Sheet2'!$E:$E,3), or LEFT('Sheet2'!$E:$E,LEN("Pen"))
Best wishes,
Hans
Hans