COUNTIFS with OR

michellelkirkland

Hi everyone. I have a formula for which i have multiple criterion. The issue I am having is for one of the columns, I want it to be counted if it contains "x" or "y" or "z". I cant figure out how to phrase it appropriately and make it work. Here is my stab at it (but it doesn't work).

=COUNTIFS({New Disposition Tracker(All CMOs) Range 2}, "serum", {New Disposition Tracker(All CMOs) Range 4}, Contains ("Unlabeled Drug Product (DP)" or ("Final Drug Product (FDP)" or (Drug Product (DP)", {New Disposition Tracker(All CMOs) Range 6}, >=[Start Date]@row, {New Disposition Tracker(All CMOs) Range 6}, <=[End Date]@row)

NicoLHC

@michellelkirkland Attention if you refer to other sheets please give a name to the coloumn to avoid issues in the future.

Please try this formula, If it does not work feel free to share the sheet with me . nico.roepnack@lighthouseconsultings.com

=COUNTIFS({New Disposition Tracker(All CMOs) Range 2}, "serum",
{New Disposition Tracker(All CMOs) Range 4}, "Unlabeled Drug Product (DP)",
{New Disposition Tracker(All CMOs) Range 6}, >=[Start Date]@row,
{New Disposition Tracker(All CMOs) Range 6}, <=[End Date]@row)
+
COUNTIFS({New Disposition Tracker(All CMOs) Range 2}, "serum",
{New Disposition Tracker(All CMOs) Range 4}, "Final Drug Product (FDP)",
{New Disposition Tracker(All CMOs) Range 6}, >=[Start Date]@row,
{New Disposition Tracker(All CMOs) Range 6}, <=[End Date]@row)
+
COUNTIFS({New Disposition Tracker(All CMOs) Range 2}, "serum",
{New Disposition Tracker(All CMOs) Range 4}, "Drug Product (DP)",
{New Disposition Tracker(All CMOs) Range 6}, >=[Start Date]@row,
{New Disposition Tracker(All CMOs) Range 6}, <=[End Date]@row)

NicoLHC

@michellelkirkland Attention if you refer to other sheets please give a name to the coloumn to avoid issues in the future.

Please try this formula, If it does not work feel free to share the sheet with me . nico.roepnack@lighthouseconsultings.com

=COUNTIFS({New Disposition Tracker(All CMOs) Range 2}, "serum",
{New Disposition Tracker(All CMOs) Range 4}, "Unlabeled Drug Product (DP)",
{New Disposition Tracker(All CMOs) Range 6}, >=[Start Date]@row,
{New Disposition Tracker(All CMOs) Range 6}, <=[End Date]@row)
+
COUNTIFS({New Disposition Tracker(All CMOs) Range 2}, "serum",
{New Disposition Tracker(All CMOs) Range 4}, "Final Drug Product (FDP)",
{New Disposition Tracker(All CMOs) Range 6}, >=[Start Date]@row,
{New Disposition Tracker(All CMOs) Range 6}, <=[End Date]@row)
+
COUNTIFS({New Disposition Tracker(All CMOs) Range 2}, "serum",
{New Disposition Tracker(All CMOs) Range 4}, "Drug Product (DP)",
{New Disposition Tracker(All CMOs) Range 6}, >=[Start Date]@row,
{New Disposition Tracker(All CMOs) Range 6}, <=[End Date]@row)

michellelkirkland

Thank you so much! That did it!

NicoLHC

@michellelkirkland it would be great if you click the 💡to share with others