Search for condition across multiple columns if date range is met

sahilhq

Hi,

I am trying to insert a formula that looks into my master sheet to find the word "3-MEETS 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)))

Paul Newcome

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.

Paul Newcome

You need to remove this closing parenthesis...

"3 - MEETS STANDARDS") <---

sahilhq

@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

Yes. Your ranges must be of the same size.

sahilhq

@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 3-MEETS STANDARDS condition across a certain row?

Paul Newcome

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.