help with % formula

[corphealth]

I have a spreadsheet that I need to reflect the percentage
of clients not utilizing our statistical services.

The formula needs to be based on the total number of
clients each sales rep has and then the number of clients
that are sending in stats and utilizing our services.

The clients names are in column A and the Sales rep in
column #E and then the stats beging in columns F - M, not
all send all 8 stats so need to figure out how to figure
out this by looking at all stat rows.

Thanks in advance for any suggestions on a formula that
will work for this.

 

[Biff]

Hi!

If I understand you correctly, you want the % of clients
for each sales rep that does not provide stats?

If that's the case:

Adjust all ranges to suit.

Say in col N enter this formula:

=COUNTA(F3:M3) and copy down as needed to cover every
client. Any returns of zero mean no stats were provided.

Then in some cell somewhere enter this formula for the %
of clients for a specific sales rep that are not providing
stats:

=SUMPRODUCT(--(E3:E12="SalesRep Name"),--(N3:N12=0))/
COUNTIF(E3:E12,"SalesRep Name)*100

Biff

 

[corphealth]

Biff,

Thanks for your suggestion, I've tried the formula >=COUNTA
(F3:M3)before, and even in rows where no stats were
entered it reflects 1 instead of 0 and where client has
listed a series of stats (which is what each is suppose to
do) it adds the set instead of just reflecting 1
reflecting they've entered stats. Once I get the first
part of the suggested formula completed, I'll go to the
next step.

Any additional suggestion will be greatly appreciated.

 

[Biff]

Hi!

COUNTA(F3:M3) will count every cell in that range that has
anything in it, both text and numbers. If it's returning 1
when it appears that the range is empty, then the range is
not empty. If 3 cells in that range have something in
them, then COUNTA(F3:M3) will return 3.

Try using =COUNTBLANK(F3:M3). If the range is empty that
should return 8. If it doesn't try this to find which cell
may have unseen characters:

=SUMPRODUCT(--(LEN(F3:M3)<1)) Then use the menu command,
Evaluate Formula (if you have that available in your
version of XL) and it will show you at which point in the
array there is something in a cell.

OR, select the range F3:M3, then hit F5, click Special,
then check Constants, OK. That should take you to the cell
that has some unseen character.

If none of those things works and you're able to, you can
send me the file to have a look. Let me know if you're
interested or able to do that and I'll post my real email
address.

Biff

 

[corphealth]

I've tried this and still doesn't seem to be working for
me. If you have the time to look at this spreadsheet and
give me your recommendations on how I can correct, I'd be
happy to send the file to you to review.

Thanks

 

[Biff]

Hi!

My ISP has a 1 meg limit so if it's larger than that, zip
it.

You can send the file to:

valko01 who hates spam is AT someplace called comcast dot
net.

Biff