Using COUNTIFS with multiple conditions

P.Bushong

Trying get a count of issues in a catergory sheet that is referenced from a main sheet and then add another condition to count the categories of only the ones where the site is checked.  The first COUNTIFS works, but not the 2nd one like below .

=COUNTIFS({Day 1 Support Log Range 1}, =[Issue Category]1, {Day 1 Support Log Range 2}, <>"closed") + COUNTIFS({Day 1 Support Log Range 3}, ={qryStatus_By_Sites Range 1}, 1)

I have attached file with screen shots and formulas.

P.Bushong

I am trying to create a report that shows the last criteria.  Perhaps there is a better way for that.  I am new to smartsheet and community, so any suggestions would be greatly appreciated.

 

 

Paul Newcome

So you are working on sheet 3. You want to look at sheet 1 and count each issue category IF the site on sheet 2 is checked as "Active"?

 

If this is the case, the easiest way of going about it is to create an additional checkbox column on sheet 1 that looks at sheet 2 and checks the box on sheet 1 if the site name is marked as active on sheet 2.

 

Something to the effect of

 

=INDEX({Sheet 2 Active Column}, MATCH([Site Name]@row, {Sheet 2 Site Column}, 0))

.

This would go into the checkbox column on sheet 1. On sheet 3, you can then add the criteria into your already established COUNTIFS to look at that checkbox column.

 

Something along the lines of

 

=COUNTIFS({Sheet 1 Issue Category}, [Issue Category]@row, {Sheet 1 New Checkbox Column}, 1)

P.Bushong

Thank you for your advise on this.  If I create the check box on sheet one, does the user have to select it?  Where does the formula for this go?  In sheet 3?

Paul Newcome

The first formula would go in the newly created checkbox column on sheet 1. It is automated, so no manual entry in this particular checkbox column will be needed once the formula is in place.

.

The second formula goes on Sheet 3 and is used in conjunction with your existing COUNTIFS formulas.

 

I am unsure of how exactly your current formulas are working, so I cannot give a specific example. Using the syntax of the COUNTIFS function though...

 

=COUNTIFS(range_1, criteria_1, range_2, criteria_2, range_3, criteria_3, .......................)

 

the range would be the new checkbox column from sheet 1 and the criteria would be 1.

P.Bushong

just to be sure.  The checkbox data type is text or checkbox?  If checkbox then I am unable to input formula.

P.Bushong

Ok, Thanks  so much.  Works now.  I had restrict only to checkbox turn on.  Once I turned off, it allows the formula.  This helps so much!  Next I will try and add the count ifs

P.Bushong

Works like a charm!!!!! Thanks so much!

Paul Newcome

Happy to help! 

P.Bushong

How can I make these formulas autofill with new record?  I can make it work if the last two cells have it, but what about in the event that the last two records are deleted?  Is there perhaps a restriction of deletion of rows to other users?

 

Andrée Starå

Hi,

You can lock the rows.

Would that work/help?

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

P.Bushong

Thanks, That's interesting.  Is there a quick way to lock all rows? And if I do lock them will this allow the users to still update cells?

Also I created this new thread on this basically asking same thing, however if I copy the last two empty rows, then the automatic field for Create (Date) is populated

https://community.smartsheet.com/discussion/auto-fill-formulas

Andrée Starå

Happy to help!

To quickly lock multiple rows you select them and right-click and select Lock.

Admin and Owner can still update the rows.

Would that work?

P.Bushong

Sorry, but no. This would not work.  The sheet is built for user input.  My concern is if they delete the last records then the formulas would disappear.

P.Bushong

Hello,

I am getting a #NOMATCH error on Sheet1 (from beginning post), when I add a new record to Sheet2.  

please see attached

Andrée Starå

What formulas are you using? Is the range looking at the whole column?