COUNTIFS Function

Jessica Howell

it comes from a data control sheet

Paul Newcome

How is it entered into the Data Control sheet and exactly how is it pulled to the formula reference sheet?

Jessica Howell

I entered the data into the "data control 2", and then The main sheet uses =VLOOKUP([Date of Incident]1, {Data Control 2 Range 2}, 3, false) to populate the proper quarter, according to the date of the incident

Paul Newcome

Ok. So it is manual entry and then VLOOKUP pulling the quarter numbers, so that shouldn't be the issue. How are the dates entered?

Jessica Howell

They are entered by form submission, manual entry

Paul Newcome

What type of column is that?

Jessica Howell

Date column

Paul Newcome

Ok. Just for testing purposes, insert a checkbox column next to the date column and enter

=IF(ISDATE([Date Column Name]@row), 1)

Are all of them checked or just some of them or even none of them?

Jessica Howell

the date column on the data control sheet?

Paul Newcome

Go ahead and do that one as well as the form sheet.

Jessica Howell

Every line that the formula was applied to, was checked

Paul Newcome

Ok. Lets try the same concept but look at the quarter columns.

=IF(ISNUMBER([Quarter Column Name]@row), 1)

Jessica Howell

Working for the quarter columns as well

Paul Newcome

Ok. So all of the data is being pulled through as it should. The formula syntax is correct. And it still isn't producing the correct count.

=COUNTIFS({Environmental Incident Tracking Range 1}, CONTAINS("Leak", @cell), {Environmental Incident Tracking Range 3}, 1, {Environmental Incident Tracking Range 4}, IFERROR(YEAR(@cell), 0) = 2020)

Hmm...

I do have a few more troubleshooting ideas left, so lets try this next one: Double check the ranges of your cross sheet references are in fact covering the correct columns and the entire column for each.

Additionally, go to the source sheet and create a filter that replicates what you are trying to accomplish with your formula.

Jessica Howell

@Paul Newcome

Hi Paul,

Trying something different. The in between dates of each quarter. Something isn't working quite right yet, but maybe an easier option. What do you think?

Q1- DEC 29, 2019- MAR 21, 2020

Q2- MAR 22, 2020- JUN 13, 2020

Q3- JUN 14, 2020- SEP 5, 2020

Q4- SEP 6, 2020- JAN 2, 2021