count the number of populated cells in column N based on data in column D

Hi Good Evening,

This is probably quite an easy one but I'm new. I am working on a spreadsheet where data will be input daily to measure KPI's. I need cell I5 on KPI's sheet to divide the number of deales JD has on finance by the total number of deals he has done. I thought I needed to a calculation in another table, which I have tried in INPUT!N183

=SUMPRODUCT( (D6:D85="JD")*(N6:N85) ) This unfortunately gives #VALUE!

Then I was going to put

=INPUT!N183/'KPI''s'!D5

in KPI's!I5

I can see that once I have this formula, my document is complete. Can anyone please help?

Thanks in advance,

Caroline

Maybe I am confused but if I am understanding this correctly you need to take the coulumn N divide it by column D then divide by 100 to get the percent.

I think your coulmn H is the total deals on finance... =(G5/D5)/100

When I set up JD's line using =(G5/D5)/100 then I get 27% for him..

I need cell I5 on KPI's sheet to divide the number of deales JD has on finance by the total number of deals he has done.

Trying to figure out how to identify the 'deals done on finance', I'm going with the 'ACC' flag in column X

If that is correct then in I5 on the KPI's sheet, try

=1-COUNTIFS(INPUT!$D:$D,"JD",INPUT!$X:$X,"ACC")/COUNTIF(INPUT!$D:$D,"JD")

This also eliminates the need for the helper table in INPUTS!M182:R187

Hi,

Thanks for the quick relpy, but no, what I am trying to achieve in N183 is the number of times there is a value (not to sum the values) in the VB column column N, when also JD appears in column D.

thanks again

caroline

Originally Posted by Richard Buttrey

...then have you tried =COUNTIF() rather than =SUMIF() ?

Yes, I thought it would be that simple too. I have tried =COUNTIF(D5:D86,"JD",O5:O86) in N183 and it says I have entered too many arguments for this function.

Cheers

Caroline

Using a similar method of formula construction to my earlier suggestion, in KPI's I5

=1-COUNTIFS(INPUT!$D:$D,C5,INPUT!$N:$N,"<>")/COUNTIF(INPUT!$D:$D,C5)

By making your 2 sheets more consistant when you enter the names, you could then use the fill handle to copy the formula down for the other sales execs.

I've completed the first column for you, hopefully that will guide you toward completing the rest.

JUNE%202012%20DOC(1).xlsx

Originally Posted by jason.b75

Using a similar method of formula construction to my earlier suggestion, in KPI's I5

=1-COUNTIFS(INPUT!$D:$D,C5,INPUT!$N:$N,"<>")/COUNTIF(INPUT!$D:$D,C5)

By making your 2 sheets more consistant when you enter the names, you could then use the fill handle to copy the formula down for the other sales execs.

I've completed the first column for you, hopefully that will guide you toward completing the rest.

Attachment 160787

Wow,

Thank you so much. I reckon I can complete it now.

Regards

Caroline