Extract count with multiple condition

[Rajender]

Hi All,


I have an excel sheet in which I have to extract the count with having multiple conditions as follows :-


1) Main data sheet is : CD_MW_Training Plan & summery needs in sheet : Snap Shot1

2) In cell D5 & E5 I need the the sum of hours meeting creteria mentioned in D4,E4,A5 respectively.

3) In first sheet Snap Shot1 from range B8:BA14, I need to count which needs to meed the criteria mentioned in from A8:A14, B7:BA7, A5,C5,& B6. total it contains 5 conditions.


I tried with sumproduct function but it showing NA error.


I uploaded the file in skydrive too.


https://skydrive.live.com/?id=393CD98CEE2A349F!106&cid=393cd98cee2a349f#cid=393CD98CEE2A349F&id=393CD98CEE2A349F!105


Thanks in advance for your suggestions.


Regards,

Rajender

 

[NARAYANK991]

Hi Rajender ,


Your worksheet is too complicated for me to work it out fully ; I'll just do it for one , and leave it to you to do the same for the rest.


First let us define several needed named ranges :

[pre]

Yes_or_No         :  ='CD_MW_Training Plan'!$I$16:$BN$342

Location          :  ='CD_MW_Training Plan'!$D$16:$D$342

Manager           :  ='CD_MW_Training Plan'!$E$16:$E$342

Training_Type     :  ='CD_MW_Training Plan'!$I$14:$BN$14

Training_Mandays  :  ='CD_MW_Training Plan'!$I$15:$BN$15
The formula for calculating the number of hours , given the following :

Selected Manager     : $A$5

Selected Course Type : $D$4

Selected Yes/No/All  : $C$5

[/pre]
is :


=SUM(MMULT(-(Manager=$A$5),-(Training_Type=$D$4))*(IF(ISNA(MATCH(Yes_or_No,IF($C$5="All",{"Yes","No"},IF($C$5="Yes",{"Yes"},{"No"})),0)),0,1))*(Training_Mandays))


entered as an array formula , using CTRL SHIFT ENTER.


Please check if this is correct , and if it is so , you can extend this to include the Location and the Course , by just multiplying these two named ranges into the above formula , as in :


=SUM(MMULT(-(Manager=$A$5)*-(Location=$A8),-(Training_Type=$D$4)*-(Courses=B$7))*(IF(ISNA(MATCH(Yes_or_No,IF($C$5="All",{"Yes","No"},IF($C$5="Yes",{"Yes"},{"No"})),0)),0,1))*(Training_Mandays))


Narayan