One purpose of having a file non-executable but setuid and/or setgid is to then use an access control list (ACL) to allow only certain users to execute that file. If you want to run a command with setuid root, but, for example, you don't want everyone to be able to run that command. If you only want certain users or certain groups to be able to run it, you might forbid execution in general then allow limited execution with an ACL.
The following example takes a fictitious command and makes it generally non-executable but setuid, then uses an ACL to allow members of the adm group to run it:
$ chown root.root privileged_command
$ chmod 4000 privileged_command
$ ls -l privileged_command
---S------ 1 root root 152104 Nov 17 21:15 privileged_command
$ setfacl -m g:adm:rx privileged_command
So, according to the file permissions, the file is setuid but not executable (or even readable). However, the access control list gives read and execute privileges to all members of the adm group.
If a member of the adm group executes the file, it will execute with the setuid privileges that the file has, under the executable privileges granted by the ACL. If anyone else tries to execute it, they will get a permission denied error.
Doing a directory listing of the file after the above setfacl command will show:
$ ls -l privileged_command
---Sr-x---+ 1 root root 152104 Nov 17 21:15 privileged_command
This seems to show that the root group has read/execute privileges on the file, but really is just indicating there is an acl that gives read/execute privileges.
This is one example of where you might want to have a program as setuid but not (normally) executable.