"Barrett was not only a great singer and piano player, but he, along with his writing partner Norman Whitfield, created an incredible body of work," Motown founder Berry Gordy said