Search for condition across multiple columns if date range is met
Hi,
I am trying to insert a formula that looks into my master sheet to find the word "3MEETS STANDARDS" across 12 columns and when this condition falls between a date range (1st Jul  15th Jul). I used the formula below and ended up getting a #UNPARSEABLE error. I can't seem to understand where I am going wrong so any help will be greatly appreciated. I have attached a screenshot of my master sheet below for your better understanding.
=COUNTIFS({SELECT ALL 12 COLUMNS}, "3  MEETS STANDARDS"), {SELECT DATE COLUMN}, AND(@cell >= DATE(2020, 7, 1), @cell <= DATE(2020, 7, 15)))
Best Answer

You could add in a helper column on the source sheet with a row level COUNTIFS to count how many fall within the range then use a SUM function to add up everything in the helper column.
Answers

You need to remove this closing parenthesis...
"3  MEETS STANDARDS") <

@Paul Newcome Many thanks for the response. Removed the paranthesis and ended up getting a #INCORRECT ARGUMENT SET error (see below a screenshot of the formula I used). Could it be because my first range of criteria is trying to select multiple columns (12 in total) and the date column which is just one column initself? The formula works fine when both the range has one columns to count the group of cells containing "3  MEETS STANDARDS"!!


@Paul Newcome Is there any formula alternative then? I am trying to look find a condition (across 12 columns) that falls under a certain date range. Can a helper column be used? If so, what formula would it contain to count all 3MEETS STANDARDS condition across a certain row?

You could add in a helper column on the source sheet with a row level COUNTIFS to count how many fall within the range then use a SUM function to add up everything in the helper column.
Help Article Resources
Categories
Check out the Formula Handbook template!