How do I call multiple columns of another sheet

Jay_CeeAUS

Hello.,

I want to calculate the number of "Fail" on a month however the issue I am having is that both the dates and the word "Fail" are on two separate columns inside of one smart sheet. Can someone assist. I've tried using the Countifs statement but isnt working The below is one sheet

this is on another sheet

rrenee

Hello,

If the Date Completed is a date column, you should use the MONTH() function to return the numerical value of the month and adjust the number for each row:

January, February, etc.

=COUNTIFS({Date Completed}, MONTH(@cell) = 1, {Test Result}, "Fail")

=COUNTIFS({Date Completed}, MONTH(@cell) = 2, {Test Result}, "Fail")

If for some reason the Date Completed column is text/number due to being the primary column, take the first two characters from the left (which is the month) and match it to the number like below:

March, April, etc.

=COUNTIFS({Date Completed}, LEFT(@cell, 2) = "03", {Test Result}, "Fail")

=COUNTIFS({Date Completed}, LEFT(@cell, 2) = "04", {Test Result}, "Fail")

I hope this helps!

Jay_CeeAUS

I don't know if I am doing something wrong here but I keep getting an #Invalid error.

below are the formulas I tried

"=COUNTIFS({OC Pen Test Data Range 2}, MONTH(@cell ) = 1, {OC Pen Test Data Range 1}, "Fail")"

and

=COUNTIFS({Date Completed}, MONTH(@cell) = 1, {Test Result}, "Fail")

Paul Newcome

Try this:

=COUNTIFS({Date Completed}, IFERROR(MONTH(@cell), 0) = 1, {Test Result}, @cell = "Fail")