Better Formula Needed

[AnilPullagura]

Hi Pros,

I have deduced a formula(kinda rough) to get my immediate request fullfilled. Now, I want to if there could be a better one to replace this not so perfect formula.

I feel there should be a good array formula to replace the below crude formula.

IF(COUNTIFS(Sheet1!$B$2:$B$65000,$B4,Sheet1!$D$2:$D$65000,"RHC",Sheet1!$E$2:$E$65000,"Completed")+
COUNTIFS(Sheet1!$B$2:$B$65000,$B4,Sheet1!$D$2:$D$65000,"Outpatient",Sheet1!$E$2:$E$65000,"Completed")=0,"",
COUNTIFS(Sheet1!$B$2:$B$65000,$B4,Sheet1!$D$2:$D$65000,"RHC",Sheet1!$E$2:$E$65000,"Completed")+
COUNTIFS(Sheet1!$B$2:$B$65000,$B4,Sheet1!$D$2:$D$65000,"Outpatient",Sheet1!$E$2:$E$65000,"Completed"))

Thanks,
Anil
Excel is more than an Ocean. It's always a Learning

 

[Aladin Akyurek]

Hi Pros,

I have deduced a formula(kinda rough) to get my immediate request fullfilled. Now, I want to if there could be a better one to replace this not so perfect formula.

I feel there should be a good array formula to replace the below crude formula.

IF(COUNTIFS(Sheet1!$B$2:$B$65000,$B4,Sheet1!$D$2:$D$65000,"RHC",Sheet1!$E$2:$E$65000,"Completed")+
COUNTIFS(Sheet1!$B$2:$B$65000,$B4,Sheet1!$D$2:$D$65000,"Outpatient",Sheet1!$E$2:$E$65000,"Completed")=0,"",
COUNTIFS(Sheet1!$B$2:$B$65000,$B4,Sheet1!$D$2:$D$65000,"RHC",Sheet1!$E$2:$E$65000,"Completed")+
COUNTIFS(Sheet1!$B$2:$B$65000,$B4,Sheet1!$D$2:$D$65000,"Outpatient",Sheet1!$E$2:$E$65000,"Completed"))

Thanks,
Anil
Excel is more than an Ocean. It's always a Learning

Try...

=SUM(COUNTIFS(
    Sheet1!$B$2:$B$65000,$B4,
    Sheet1!$D$2:$D$65000,{"RHC","Outpatient"},
    Sheet1!$E$2:$E$65000,"Completed"))

Custom format the formula cell as e.g.: [=0]"";General

 

[AnilPullagura]

Thanks Aladin, it worked,

However I have more criteria than just RHC and Outpatient, Can we have any better array formula...., that i need not hardcode..., and can dynamically do...

Help needed...

Thanks,
Anil

 

[Aladin Akyurek]

Thanks Aladin, it worked,

However I have more criteria than just RHC and Outpatient, Can we have any better array formula...., that i need not hardcode..., and can dynamically do...

Help needed...

Thanks,
Anil

Create a range housing the relevant criteria like RHC, Outpatient, etc. Name this range CritList, and invoke:

=SUMPROPDUCT(COUNTIFS(
    Sheet1!$B$2:$B$65000,$B4,
    Sheet1!$D$2:$D$65000,CritList,
    Sheet1!$E$2:$E$65000,"Completed"))

 

[T. Valko]

Thanks Aladin, it worked,

However I have more criteria than just RHC and Outpatient, Can we have any better array formula...., that i need not hardcode..., and can dynamically do...

Help needed...

Thanks,
Anil

Use cells to hold the criteria:

• A2 = RHC
• A3 = Outpatient
• B2 = Completed

Then:

=SUMPRODUCT(--(Sheet1!B2:B65000=B4),--(ISNUMBER(MATCH(Sheet1!D2:D65000,A2:A3,0))),--(Sheet1!E2:E65000=B2))