Using COUNTIFS
Smartsheet 1 is used to book a desk in our office. It has a DATE column with every working day this year (date format). It then has a column per desk or group of desks in our office. These columns use the contact format. Some columns allow multiple contacts per cell. To book a desk you scroll to the row (date) and then scroll across to fund the desk (or group of desks) you want and add your name.
Smartsheet 2; I want to show which day(s) I was booked into the office. This formula returns the number of times my name appears in Smartsheet 1. The range {} is all the desk / group of desk columns in Smartsheet 1. I want to create a report showing each staff member, and how which day(s) they were in the office.
=COUNTIFS({Book your coworking space - Jan to Jun Users}, HAS(@cell, Name@row))
BUT, if I add a second condition I get "#incorrect argument set"). The new date range "Book your... - date" is the column containing the DATE in Smartsheet 1.
=COUNTIFS({Book your coworking space - Jan to Jun Users}, HAS(@cell, Name@row), {Book your coworking space - date}, Date@row)
I have also tried - same error
=COUNTIFS({Book your coworking space - Jan to Jun Users}, HAS(@cell, Name@row), {Book your coworking space - date}, HAS(@cell,Date@row))
Question - how do I get a formula to count the number of times my name appears on a specified row
Best Answer
-
We've found the root cause of the issue!
Essentially {These} references have to match in the exact same number of cells. The ranges need to be identical... so if you have one column of 10 rows with dates, you cannot then select a second range of 10 columns with 10 rows, these sizes don't match.
This means that each range would need to be a single column, since your date column is a single column. You note that the Range "...Entire Users Column" is all columns containing staff names. How many columns do you have?
If you have more than one Contact column in the sheet, you'll need to set up individual cross-sheet references per column and search for your Contact and Date in each one:
=COUNTIFS(first combo) + COUNTIFS(second combo) + COUNTIFS(third combo)... etc
Try something like:
=COUNTIFS({1st User Column}, HAS(@cell, Name@row), {Book your coworking space - all dates}, Date@row) + COUNTIFS({2nd User Column}, HAS(@cell, Name@row), {Book your coworking space - all dates}, Date@row) + COUNTIFS({3rd User Column}, HAS(@cell, Name@row), {Book your coworking space - all dates}, Date@row)
Answers
-
The first COUNTIFS structure is the correct one:
=COUNTIFS({Book your coworking space - Jan to Jun Users}, HAS(@cell, Name@row), {Book your coworking space - date}, Date@row)
The error #incorrect argument set can appear if the {ranges} aren't the same size... for example if you select 3 cells in one range and an entire column in the other range.
It sounds like you may only have "Jan - Jun" selected in your first range. Can you double check that both the Date range and the Users range are fully selected as an entire column? Or, that both ranges are only selected from Jan - Jun? You can view the ranges in the Sheet Reference Manager.
Let me know if this helped!
Cheers,
Genevieve
-
Many thanks Genevieve,
I have updated the references and the formula - the new reference "...ALL DATA" covers all columns/ data in the sheet.
=COUNTIFS({Book your coworking space - ALL DATA}, HAS(@cell, Name@row), {Book your coworking space - ALL DATA}, HAS(@cell, Date@row))
This no longer returns the error, it returns 0 (zero). This is not correct. So I split it into two parts....
The below formula returns 95, i.e. my name is within the new range 95 times. This is correct.
=COUNTIFS({Book your coworking space - ALL DATA}, HAS(@cell, Name@row))
The below formula returns 1, i.e. the date only appears once. This is correct.
=COUNTIFS({Book your coworking space - ALL DATA}, HAS(@cell, Date@row))
When put together I am expecting the result of 1 (the name and the date match) or 0 (no match).
Extract of sheet below...my name is highlighted, others blacked out.
-
I'm glad that the error no longer appears! One thing sorted.
We don't need to use HAS for the Date since HAS is for multi-select columns. Try looking for the date directly:
=COUNTIFS({Book your coworking space - ALL DATA}, HAS(@cell, Name@row), {Book your coworking space - ALL DATA}, Date@row)
If this doesn't return the correct result, can you confirm how you have the Date column set up in your current sheet, and the sheet you're looking in to? I presume they are both set as a Date Type of column, is that correct?
Cheers,
Genevieve
-
Thanks @Genevieve P.
Have updated the formula and confirmed the columns are both DATE type. No change, still returns 0 (zero) instead of 1 (one).
-
Can you post a screen capture of the sheet where you're building the formula? How is the Date you're searching for entered in your Date column? (ex. are you using the TODAY() function or are you manually entering it?)
-
Oh genius - had not thought of that!
The source sheet is using a formula to populate the date. I changed it to the actual date. Sadly it did not fix the issue. Both sheets have the date directly entered for 28-Mar-2022.
Have cleared cache and logged out/ in again. This has not fixed the issue either.
Below is the sheet with the formulas (in the COUNT column)...I have copied them into the NOTES column so you can see
-
My apologies! I just realized you're using the same {range} for both columns. You'll want two separate columns and two separate ranges, one for the Contact and one for the Date.
We just need to make sure these ranges are the same Length, not that they include the same columns, does that make sense?
ex:
{Book your coworking space - Jan to Jun Users}
and
{Book your coworking space - Jan to Jun Dates}
OR
{Book your coworking space - Entire Users Column}
and
{Book your coworking space - Entire Date Column}
-
Okay, so have deleted the old references and started afresh and created 2 new ranges.
The formulas work independently:
Formula: =COUNTIFS({Book your coworking space - entire users column}, HAS(@cell, Name@row))
Formula: =COUNTIFS({Book your coworking space - all dates}, Date@row)
Range "...Entire Users Column" is all columns containing staff names
Range "...Date" is a single column with the date in it
Note: the range "...Entire Users Column" does not include the DATE column and vice versa
BUT, when I merge...
=COUNTIFS({Book your coworking space - entire users column}, HAS(@cell, Name@row), {Book your coworking space - all dates}, Date@row)
#incorrect argument set
=COUNTIFS({Book your coworking space - entire users column}, HAS(@cell, Name@row), {Book your coworking space - all dates}, Date@row))
#unparseable
-
We've found the root cause of the issue!
Essentially {These} references have to match in the exact same number of cells. The ranges need to be identical... so if you have one column of 10 rows with dates, you cannot then select a second range of 10 columns with 10 rows, these sizes don't match.
This means that each range would need to be a single column, since your date column is a single column. You note that the Range "...Entire Users Column" is all columns containing staff names. How many columns do you have?
If you have more than one Contact column in the sheet, you'll need to set up individual cross-sheet references per column and search for your Contact and Date in each one:
=COUNTIFS(first combo) + COUNTIFS(second combo) + COUNTIFS(third combo)... etc
Try something like:
=COUNTIFS({1st User Column}, HAS(@cell, Name@row), {Book your coworking space - all dates}, Date@row) + COUNTIFS({2nd User Column}, HAS(@cell, Name@row), {Book your coworking space - all dates}, Date@row) + COUNTIFS({3rd User Column}, HAS(@cell, Name@row), {Book your coworking space - all dates}, Date@row)
-
Success!
Thank you so much for your time on this.
=COUNTIFS({Book your coworking space - IT&T users}, HAS(@cell, Name@row), {Book your coworking space - date}, Date@row)
-
Well done!!! 🎉
Glad we got there in the end, and I'm happy to help. 🙂
Help Article Resources
Categories
Check out the Formula Handbook template!
