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:andree@workbold.com | 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.
-
Happy to help. 👍️
-
It worked - thanks Paul
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!