SOLVED

How to use COUNTIFS function if it overlaps between with different criteria

%3CLINGO-SUB%20id%3D%22lingo-sub-3189067%22%20slang%3D%22en-US%22%3EHow%20to%20use%20COUNTIFS%20function%20if%20it%20overlaps%20between%20with%20different%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3189067%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Excel%20Community%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20reading%20this%20question%2C%20I%20think%20it%20might%20be%20easier%20to%20explain%20if%20you%20could%20kindly%20open%20the%20attached%20excel.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EBackground%3C%2FSTRONG%3E%3A%20I%20have%20a%20set%20of%20different%20clinical%20trials%20that%20needs%20to%20be%20evaluated%20and%20categorize%20into%20different%20groups.%20Group%201%20means%20they're%20clinical%20trials%20that%20include%20patients%20with%20moderate%20and%20severe%20Covid-19%20patients.%20Group%201%2B2%20means%20they're%20clinical%20trials%20that%20further%20narrows%20only%20specifically%20moderate%20Covid-19%20patients.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EGoal%3C%2FSTRONG%3E%3A%20I%20would%20like%20to%20create%20one%20column%20of%20this%20Relevance%20Grouping.%20And%20then%20create%20the%20table%20seen%20in%20row%2022%20and%2023%20where%20I%20can%20analysis%20the%20%23%20of%20total%20trials%20and%20median%20RR.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3ECurrent%20Issue%20and%20Question%3A%3C%2FSTRONG%3E%20Because%20of%20the%20Relevance%20Groups%20that%20overlaps%20%221%2B2%22%2C%20I%20don't%20know%20how%20to%20use%20excel%20formula%20in%20this%20situation.%20Could%20someone%20kindly%20show%20me%20how%20to%20resolve%20this%20with%20formula%20in%20Row%2027%20and%2028%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20so%20much%2C%3C%2FP%3E%3CP%3EAmy%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3189067%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3189093%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20use%20COUNTIFS%20function%20if%20it%20overlaps%20between%20with%20different%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3189093%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1260738%22%20target%3D%22_blank%22%3E%40AmyYang%3C%2FA%3E%26nbsp%3BSee%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3189147%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20use%20COUNTIFS%20function%20if%20it%20overlaps%20between%20with%20different%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3189147%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDear%20Riny%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20so%20much%20for%20your%20time%20to%20respond%20and%20provide%20your%20feedback%20within%20the%20excel!%20That%20is%20very%20helpful%20however%20I%20realized%20that%20my%20original%20data%20set%20includes%20other%20categories%20as%20well%20as%20blank%20cells%20which%20are%20uncategorized.%20I%20have%20revised%20the%20sample%20Raw%20Data%20in%20the%20attach%20excel%20document%20in%20yellow%20highlighted%20rows.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EFollow-up%20questions%3A%3C%2FSTRONG%3E%3CBR%20%2F%3E1)%20Could%20you%20teach%20me%20how%20to%20count%20for%20the%20number%20of%20studies%20that%20are%20in%20Group%201%20and%202%20when%20the%20original%20data%20set%20has%20more%20categories%20involved%3F%3CBR%20%2F%3E2)%20And%20similarly%20for%20finding%20the%20median%20RR%3F%20Looking%20at%20your%20excel%20formula%2C%20I%20tried%20to%20use%20a%20similar%20approach%20to%20find%20the%20median%20RR%20such%20as%20this%20one%2C%20but%20excel%20did%20not%20accept%20it%3A%3CBR%20%2F%3E%3DMEDIAN(FILTER(Table8%5BRR%5D%2CTable8%5BRelevance%20Group%5D%3D1%20or%20%221%2B2%22))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20kindly%2C%3CBR%20%2F%3EAmy%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3189154%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20use%20COUNTIFS%20function%20if%20it%20overlaps%20between%20with%20different%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3189154%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1260738%22%20target%3D%22_blank%22%3E%40AmyYang%3C%2FA%3E%26nbsp%3BSee%20attached.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20key%20to%20all%20these%20calculations%20is%20the%20FILTER%20function.%20You%20can%20study%20the%20help%20pages%20to%20gain%20an%20understanding%20on%20how%20this%20function%20works.%20Combining%20criteria%20with%20the%20%2B%20sign%20works%20like%20a%20logical%20OR%20operation.%20When%20you%20use%20the%20*%20sign%20it's%20like%20AND.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOnce%20you%20have%20narrowed%20down%20the%20dataset%20by%20filtering%2C%20you%20can%20apply%20COUNTA%20and%20MEDIAN.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3215913%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20use%20COUNTIFS%20function%20if%20it%20overlaps%20between%20with%20different%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3215913%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3BHi%20Riny%2C%20thanks%20so%20much%20for%20your%20follow-up%20feedback%2C%20that%20makes%20a%20lot%20of%20sense!%20Thanks%20for%20sharing%20and%20teaching.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%2C%3C%2FP%3E%3CP%3EAmy%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

Hi Excel Community,

 

Thank you for reading this question, I think it might be easier to explain if you could kindly open the attached excel. 

 

Background: I have a set of different clinical trials that needs to be evaluated and categorize into different groups. Group 1 means they're clinical trials that include patients with moderate and severe Covid-19 patients. Group 1+2 means they're clinical trials that further narrows only specifically moderate Covid-19 patients. 

 

Goal: I would like to create one column of this Relevance Grouping. And then create the table seen in row 22 and 23 where I can analysis the # of total trials and median RR.

 

Current Issue and Question: Because of the Relevance Groups that overlaps "1+2", I don't know how to use excel formula in this situation. Could someone kindly show me how to resolve this with formula in Row 27 and 28?

 

Thanks so much,

Amy

4 Replies

@Riny_van_Eekelen 

 

Dear Riny,

 

Thanks so much for your time to respond and provide your feedback within the excel! That is very helpful however I realized that my original data set includes other categories as well as blank cells which are uncategorized. I have revised the sample Raw Data in the attach excel document in yellow highlighted rows.

 

Follow-up questions:
1) Could you teach me how to count for the number of studies that are in Group 1 and 2 when the original data set has more categories involved?
2) And similarly for finding the median RR? Looking at your excel formula, I tried to use a similar approach to find the median RR such as this one, but excel did not accept it:
=MEDIAN(FILTER(Table8[RR],Table8[Relevance Group]=1 or "1+2"))

 

Thank you kindly,
Amy

best response confirmed by AmyYang (Contributor)
Solution

@AmyYang See attached.

 

The key to all these calculations is the FILTER function. You can study the help pages to gain an understanding on how this function works. Combining criteria with the + sign works like a logical OR operation. When you use the * sign it's like AND.

 

Once you have narrowed down the dataset by filtering, you can apply COUNTA and MEDIAN.

 

@Riny_van_Eekelen Hi Riny, thanks so much for your follow-up feedback, that makes a lot of sense! Thanks for sharing and teaching. 

 

Regards,

Amy