Countifs Formula

Michelle McN

Hi

I am trying to count the number of times a training course has been scheduled.

The training schedule has the following columns:

Training Course Name (one column) (range 1)
Training Dates from 29 May to 18 Aug (which is 82 columns in total) (range 2). A scheduled training session is marked with an 'X'

So I'm trying to calculate how many times an 'X' occurs in a training course row.

In my calculation document the primary column is a list of each training course and I used this as the criteria for range 1.

I've tried using a COUNTIFS formula as follows however I get an unparseable response:

=COUNTIFS({WAVE1B Learning Schedule Test Range 2}, "X" , {WAVE1B Learning Schedule Test Range 1}, [Primary Column]@row))

I can get the first part of the calculation to work and it returns the total number of 'X' in the schedule. Once I add in the training session name the formula stops working. I used the help function provided by Smartsheet when selecting cross sheet references etc.

Thanks

Michelle

Hollie Green

You have an extra ) at the end of your formula.

Michelle McN

1. Thanks Hollie - I took the extra ) out and now I get an #incorrect argument response. I'm sure the reason is because "For functions that take two ranges: The range sizes don’t match for the function." I've tried using AND but can't seem to get it to work.

Andrée Starå

Hi @Michelle McN

I hope you're well and safe!

Can you share some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help.

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Michelle McN

Hi Andree

Here are some screen shots:

Range 1 - course name column

Range 2 - course date details - this range is 82 columns wide

The formula is now:

=COUNTIFS({WAVE1B Learning Schedule Test Range 2}, "X", {WAVE1B Learning Schedule Test Range 1}, [Primary Column]@row)

but is now returning

Paul Newcome

The problem is that you have two different range sizes within the same function. All ranges in a function must be of the same size. So if one range is a single column, all ranges must be a single column, or if one range is multiple columns, then the other range must be the same number of columns.

You will need to create a helper column on the source sheet that counts how many Xs you have on each row. Then you will be able to use a SUMIFS on your metrics sheet.

Source Sheet Helper Column:

=COUNTIFS([First Column]@row:[Last Column]@row, @cell = "X")

Metrics Sheet Formula:

=SUMIFS({Source Sheet Helper Column}, {Source Sheet Course Column}, @cell = [Primary Column]@row)

Michelle McN

Thanks @Paul Newcome - I will give this a go.

Paul Newcome

Happy to help. 👍️

Michelle McN

It worked - thanks Paul