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

Find more posts tagged with

Formulas

Accepted answers

Jeff Reisman

@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)

All comments

Jeff Reisman

@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)

Dell55

@Jeff Reisman

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 ?

Jeff Reisman

@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.

Dell55

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😒

Dell55

@Jeff Reisman

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

Jeff Reisman

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)

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.