Excel Sum for drop down list

[Vpremakumar]

I need a formula to do sum of tasks for a particular client if the status is closed, client name is listed from the drop down menu. If i select a client A (just for example) from drop down list then the formula should do the sum of closed tasks in a separate cell, if I change to client B than cell should show the closed tasks for client B. Please suggest me. Thanks

 

[Luke M]

Replace DropCell with actual cell reference, but something like this should work:


=SUMPRODUCT(--(ListOfClientNames=DropCell),--(ListOfStatus="Closed")


Guessing at cell references, this might be:


=SUMPRODUCT(--($A$2:$A$100=C1),--($B$2:$B$100="Closed")

 

[Vpremakumar]

hey Luke, this is not working, i'm getting "0" answer.can u explain in detail. basically formula should refer to one column for client name, one column for status. If I select particular client in drop down list, formula should count( do the sum) of closed tasks for the client selected in drop down list. If you want I can send my excel sheet too you. one more thing is drop down cell in one sheet and data in another sheet like cover page is having drop down list with result display and background page having all the data.

 

[NARAYANK991]

Hi Premakumar ,


Luke's formula does whatever it is supposed to do.


The formula supposes that the list of client names is in column A , in the range A2:A100. It also supposes that the client name drop-down selection is in cell C1. The client status is assumed to be in column B , from B2:B100.


If you are getting 0 as the result , please check whether the above assumptions are correct ; if not change the cell references according to your requirement.


Also , the status text may not be exactly "Closed" ; it may have extra spaces before or after the text , or it may have been specified as "Close" ; please check your data.


Narayan

 

[Vpremakumar]

hello,

let me try, in addition to the status and formula should look for the closed months also.. for example formula should count all the closed documents in JAN for client A and next formula should count all the on-time closed documents in Jan for client A, so that i can calculate the % on-time fir client A in JAN. I need below listed values


Month : JAN

Total Documents submitted

Total error free documents

Quality Index = (Total error free documents/Total Documents submitted)*100

Total Documents submitted

Total documents on-time

On-time Delivery = (Total documents on-time /Total Documents submitted ) * 100

 

[NARAYANK991]

Hi Premakumar ,


I am not able to understand whatever you have explained.


Can you upload your workbook and post the access link here ? Or else , mail me the workbook at narayank1026@gmail.com


Narayan