COUNTIFS Using And as well as Or

Paul Newcome · 06/15/20

=IF(COUNTIFS([Study Setup]2:[Third Party Labs]2, "In Progress") + COUNTIFS([Study Setup]2:[Third Party Labs]2, "Completed") >= 3, "Green", IF(COUNTIFS([Study Setup]2:[Third Party Labs]2, "In Progress") + COUNTIFS([Study Setup]2:[Third Party Labs]2, "Completed") = 2, "Yellow", "Red"))

The parenthesis were not the only issue though. It looks like you have "smart quotes" around the word "Yellow". Notice the slanting and formatting that shows open vs closed quotes as opposed to the straight up and down quotes everywhere else? Those are smart quotes and can't be used in formulas in Smartsheet. I went ahead and replaced those in the above as well.

Alicia Jones · 08/27/20

Hi!

I am trying to reference another sheet and collect all statuses that are completed within a date range, I am trying to use countifs but it is not working:

=COUNTIFS(({Status}, @cell = "Complete", AND({End Date}, @cell >= Start1, {End Date}, @cell <= Finish1))

Any advice would be appreciated!

Alicia Jones · 08/28/20

https://community.smartsheet.com/discussion/comment/261223#Comment_261223

I actually figured it out: =COUNTIFS({Status}, @cell = "Complete", {End Date}, @cell >= Start1, {End Date}, @cell <= Finish1)

Alexia Saphos · 11/18/20

Hi @Paul Newcome ,

I am having an issue with my formula:

=COUNTIFS([Start Date]:[Start Date], IFERROR(MONTH(@cell), 0) >= 7, Type:Type, OR(@cell = "Residence Life Event", @cell = "6 in first 6", @cell = "Welcome Back Event", [P.A.W.S (Res Life Only)]:[P.A.W.S (Res Life Only)], "Philanthropic"))

I want it to count events that are equal to or greater than a start date of July (7), if the Type is selected as either a Residence Life Event, 6 in first 6, or Welcome Back Event, and if the column P.A.W.S (Res Life Only) is selected as Philanthropic.

Paul Newcome · 11/18/20

@Alexia Saphos It is simply a case of a misplaced parenthesis. One needs moved from the end of the formula to close out the OR statement.

=COUNTIFS([Start Date]:[Start Date], IFERROR(MONTH(@cell), 0) >= 7, Type:Type, OR(@cell = "Residence Life Event", @cell = "6 in first 6", @cell = "Welcome Back Event"), [P.A.W.S (Res Life Only)]:[P.A.W.S (Res Life Only)], "Philanthropic")

Alexia Saphos · 11/18/20

Thank you, @Paul Newcome . I added the parenthesis and am still getting #UNPARSEABLE

=COUNTIFS([Start Date]:[Start Date], IFERROR(MONTH(@cell), 0) >= 7, Type:Type, OR(@cell = "Residence Life Event", @cell = "6 in first 6", @cell = "Welcome Back Event"), [P.A.W.S (Res Life Only)]:[P.A.W.S (Res Life Only)], "Philanthropic"))

Paul Newcome · 11/18/20

That's because you didn't want to ADD a parenthesis. You wanted to MOVE a parenthesis. Take a look at the formula I provided. There should only be one at the end.

SammyMorey · 01/04/21

Sorry all fixed my issue and can not delete my comment - ignore me :)

Ruby Munoz · 01/05/21

@Paul Newcome Hello Paul, I was wondering if you would be able to assist, I have been reviewing this thread to see what formula best fits my smartsheet. I am wanting to reference a second sheet and star the what is found. In essence, I would like to set it up as: If the employee ID is found on sheet 1 and on sheet 2 mark the star if it is not found don't mark the star. I am just not sure how to tie this statement in an AND statement to reference the second sheet. I was able to do the first step and it worked....I just don't know how to tie in the second sheet . This is what I have so far...

=IF(COUNTIFS({Employee ID 1}, [Employee ID]@row) > 0, 1, 0)

Any help from anyone is greatly appreciated!

Paul Newcome · 01/05/21

@Ruby Munoz I'm not sure I follow. Are you working with 3 sheets total? If the employee id is found on sheets 1 and 2, then highlight the start on sheet 3?

Ruby Munoz · 01/12/21

@Paul Newcome Hi Paul, thank you for responding. Yes, thats correct. I am working on 3 sheets.

Paul Newcome · 01/12/21

@Ruby Munoz Ok. So if you need to to be on BOTH sheets, you would use two separate COUNTIFS along with an AND.

=IF(AND(COUNTIFS({Sheet 1}, [Employee ID]@row) > 0, COUNTIFS({Sheet 2}, [Employee ID]@row) > 0), 1)

Erin Ballantine · 08/20/21

Can you use Countif with an immediate Or statement? In other words, if I wanted to count every item that had a [Finish Date] of mm/dd/yyyy OR a [Complete Date] of mm/dd/yyyy?

Paul Newcome · 08/20/21

@Erin Ballantine You would need to write two separate COUNTIFS statements (one for each column) and then add them together.

=COUNTIFS([Finish Date]:[Finish Date], @cell = DATE(2021, 01, 01)) + COUNTIFS([Complete Date]:[Complete Date], @cell = DATE(2021, 01, 01))

shuckel · 10/11/21

I'm trying to create a formula that will tell me when something for the same row has something specific in two separate columns. I can't figure out what the formula should be... I've tried the formula below but I have two problems: (1) it comes back "#unparseable" and (2) I'm not specifying that I need these things to be true for the same row.

In the picture, I've provided some dummy info to explain what I'm looking for. Using this, how can I pull the total number of rows where the ColumnA = "A" and ColumnB = "identify" for the same row? (The answer in this screenshot is 2.)

Smartsheet Question - 2021.10.11.jpg

Here's what I tried:

=COUNTIF([ColumnA]:[ColumnA], "A") AND ([ColumnB]:[ColumnB], "identify")

COUNTIFS Using And as well as Or

Comments

Help Article Resources

Categories