Forum Discussion
How to use COUNTIFS function if it overlaps between with different criteria
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.
AmyYang See attached.
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
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