Using COUNTIFS

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)

All comments

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.

Hi @James Johnson-Miller

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}

{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!

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:

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)