# how to use COUNTIFS and OR together

Trying to create a formula to collect a number of specific rows added in a separate sheet in the last week, last 2 weeks, etc. I have a formula that works for getting the number for this week :

=COUNTIFS({Week Number}, [Todays Week ]@row, {OT Student Code}, [Student Code]@row)

but I'm having trouble getting it to work. I was thinking the same formula but at a week OR week-1 to one of the criterion.

=COUNTIFS(OR([Todays Week ]@row, [Todays Week ]@row - 1), [Todays Week ]@row, {OT Student Code}, [Student Code]@row)

Any tips would be Awesome.

thanks!

Dell

Tags:

• @Dell55 The syntax for OR is easy to mess up. Try this:

=COUNTIFS({Week Number}, OR(@cell = [Todays Week ]@row, @cell = ([Todays Week ]@row - 1)), {OT Student Code}, [Student Code]@row)

Regards,

Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• @Dell55 The syntax for OR is easy to mess up. Try this:

=COUNTIFS({Week Number}, OR(@cell = [Todays Week ]@row, @cell = ([Todays Week ]@row - 1)), {OT Student Code}, [Student Code]@row)

Regards,

Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• thanks!! works perfect now...

But now I realize I need another part of the formula to exclude rows with a certain check box "absent" Is that pretty easy to add a criterion ?

• @Dell55 yes, you just add the range/criteria pair:

=COUNTIFS({Week Number}, OR(@cell = [Todays Week ]@row, @cell = ([Todays Week ]@row - 1)), {OT Student Code}, [Student Code]@row, {Checkbox column}, 0)

This will exclude rows where the checkbox in the checkbox column range you select is unchecked. To exclude rows where the box is checked, change the 0 to a 1.

Regards,

Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• Tried to add the absent checkbox column

=COUNTIFS({Week Number}, [Todays Week ]@row, {OT Student Code}, [Student Code]@row, {absent}, 0)

its giving me an #incorrectargument error

cant see where I went astray😒

• OK i see thats the wrong formula up there... this is what I am trying to do

=COUNTIFS({Week Number}, OR(@cell = [Todays Week ]@row, @cell = ([Todays Week ]@row - 1)), {OT Student Code}, [Student Code]@row, {absent}, 0)

Now I cant see where I went astray

• Try using the @cell = in the criteria:

=COUNTIFS({Week Number}, OR(@cell = [Todays Week ]@row, @cell = ([Todays Week ]@row - 1)), {OT Student Code}, [Student Code]@row, {absent}, @cell = 0)

Regards,

Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!