COUNTIFS formula not working for larger data set

Isis Taylor

I have a COUNTIFS formula that works when looking at a single column, however when I expand the formula to include a full range of columns (37), I receive an Incorrect Argument Set error.

The formula below looks at a column and essentially counts all values as long as it is not blank. It also compares it to a date column on the sheet and counts any matches for the MAX date (not greater than today). It returns the correct count. **This is a sheet summary formula and the bold value below refers to a field within the summary that calculates the MAX date**

=COUNTIFS(BIA:BIA, OR(CONTAINS("Pass", @cell), CONTAINS("Fail", @cell), CONTAINS("N/A", @cell)), [Audit Date]:[Audit Date], [Audit Date]#)

However, this formula gives me an error. I've tried the formula many different ways and received various errors.

=COUNTIFS(BIA:[Wave #], OR(CONTAINS("Pass", @cell), CONTAINS("Fail", @cell), CONTAINS("N/A", @cell)), [Audit Date]:[Audit Date], [Audit Date]#)

I'm sure I'm writing it wrong, but I can't figure it out.

Paul Newcome

Ok. So unfortunately there are only two options (neither of which are ideal).

First is to insert 37 more helper columns and then use a basic formula to replicate the [Audit Date]@row. Of course you can hide these helper columns after setting everything up. Then your COUNTIFS would look more like this:

=COUNTIFS(BIA:[Wave #], OR(………….), [First Helper Audit Date]:[Last Helper Audit Date], [Audit Date]#)

The second option is (in my opinion) the least desirable of the two. Basically you set up separate COUNTIFS for each column and then add them together.

=COUNTIFS(first column) + COUNTIFS(second column) + COUNTIFS(third column) + ………………………

Paul Newcome

Your ranges within the same function must be of the same shape and size. If one range is 10 whole columns, all ranges must be 10 whole columns.

How many columns are covered by your first range, and how many columns do you have total in your sheet already?

Isis Taylor

@Paul Newcome There are 46 columns on the total sheet. The first range covers 37 columns each with 17 rows of data. The second range looks at 1 column (Audit Date), also with 17 rows of data. The remaining 8 columns on the sheet which are not included in the formula all have 17 rows of data with the exception of the Primary column which is blank, and the comments column.

Paul Newcome

Ok. So unfortunately there are only two options (neither of which are ideal).

First is to insert 37 more helper columns and then use a basic formula to replicate the [Audit Date]@row. Of course you can hide these helper columns after setting everything up. Then your COUNTIFS would look more like this:

=COUNTIFS(BIA:[Wave #], OR(………….), [First Helper Audit Date]:[Last Helper Audit Date], [Audit Date]#)

The second option is (in my opinion) the least desirable of the two. Basically you set up separate COUNTIFS for each column and then add them together.

=COUNTIFS(first column) + COUNTIFS(second column) + COUNTIFS(third column) + ………………………

Isis Taylor

Rats. That's what I was thinking last night, but I was hoping there was a way around it. 😣I'm glad you're the one that responded, I almost reached out to you directly.

Paul Newcome

Always happy to help. 👍️