Countifs Formula
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
Answers

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

 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.

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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:[email protected]  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

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

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)

Thanks @Paul Newcome  I will give this a go.

It worked  thanks Paul
Help Article Resources
Categories
Check out the Formula Handbook template!