Help with COUNTIFS

Ty Werven

So I am trying to make a metric sheet that uses a COUNTIFS sorting by month, year, lane, and to only count if approve is check. I will make a dashboard that eventually sorts by the lanes (3 of them in total) and it will be tracked for trends in change in months and years. Below is the sheet that is connected to a form where data gets dumped into. I will have multiple different of these sheets because I have many different units. So again, I will want the countifs taking both the month and year in the date, the lane (1,2, or 3), and I only want it to work if the box is checked.

These last 2 pictures are the metric sheet I am making. here is a zoomed in picture of the first table I will use which is for lane 1. The second picture is the formula i have been trying to tinker with. I know I have to add a countifs to sort the three different types of lanes also into it. If anyone could help me with this it would be greatly appreciated! I can also provide more information if that would help. Thanks!

bisaacs

Hey @Ty Werven,

I think your issue is the Year Column isn't the full typed out year. I just tested COUNTIFS using MONTH/YEAR, and it didn't work if I just had "24", but it worked if I typed out "2024", so maybe try that?

Ty Werven

Here is an update where I am at. I changed the check box column to a yes or no selection instead to see if that would work. I am getting an INVALID OPERATION error. Thanks

bisaacs

Hey @Ty Werven,

The checkbox column uses a "1" or "0" to determine if it's checked or not. So you'll want to change the "Checked" criteria to 1 (numerical, not as text).

When you're trying to use multiple criteria in a COUNTIFS, you need to wrap the criteria in either an AND or OR. So for your Month/Year requirement, wrap those two in an AND function and it should work.

Hope the helps!

Ty Werven

Hello @bisaacs,

Thanks for the help!

Could you help me with the AND criteria in the formula? I decided to just use "Approve" and "Deny" instead of the checkbox also. I have never used an AND statement so I would greatly appreciate the help!

bisaacs

Hey @Ty Werven,

Absolutely! I think I actually made it harder by using AND on you, so apologies! Since you're just making sure all criteria matches (in this case making sure the month and year matches as well), then I would just have the formula look like this:

=COUNTIFS({6AB Range 3}, "Lane 1", {6AB Range 1}, "Approve", {6AB Range 2}, MONTH(@cell) = $[Primary Column]@row, {6AB Range 2}, Year(@cell) = $[Column2]@row)

Hope this helps!

Ty Werven

@bisaacs,

I Still can not get it to work for some reason. Here is my copied line. And below I have my metric sheet and where I'm pulling columns from. Have any idea what I am doing wrong? Thanks!

=COUNTIFS({6AB Range 3}, "Lane 1", {6AB Range 1}, "Approve", {6AB Range 2}, MONTH(@cell) = $[Primary Column]@row, {6AB Range 2}, YEAR(@cell) = $[Column2]@row)

bisaacs

Hey @Ty Werven,

I think your issue is the Year Column isn't the full typed out year. I just tested COUNTIFS using MONTH/YEAR, and it didn't work if I just had "24", but it worked if I typed out "2024", so maybe try that?

Ty Werven

@bisaacs,

It worked! I really appreciate your help!