sum if and match range of cells in
Hello, I am trying to sum rows in a column if a text in 1 column matches text in a range in another column. And have that number show up in a Summary field.
If we look at the sheet, I want to sum values in the "# of Views" column if the data in "Workspace Title" match a range of cells in the Workspace column. So the formula would look at row 2 and 30, see that both term sin Workspace Title column match the children under "Professional Skills Development. So therefor return 16 in the Summary field PSD.
I have tried sum , sumifs, sum(collect. But I just can't figure it out. The best I can do is =SUMIF([Workspace Title]:[Workspace Title], HAS(@cell, Workspace2:Workspace30), [# of Views]:[# of Views]). I get a return of 0.
Please help
Answers

I actually figured this out. Not sure how to delete this discussion.

If I'm understanding you correctly, you want a formula that SUMs together the values in your Total column if either of the Workspace columns ("Workspace" or "Workspace Title") have a specific matching value, is that correct?
If so, I would personally write these as separate Summary Fields pervalue, like so:
This would be two SUMIF formulas added together  one for the Workspace column and one for the Workspace Title column, evaluating each cell individually.
=SUMIF(Workspace:Workspace, "Professional Skills Development", [# of Views]:[# of Views]) + SUMIF([Workspace Title]:[Workspace Title], "Professional Skills Development", [# of Views]:[# of Views])
This assumes you will not have one row with the same value, otherwise the # of Views will be duplicated. Does that make sense?
Cheers!
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
Thank you for the help and explaining it to me so simply. The formula works perfect.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.4K Get Help
 364 Global Discussions
 200 Industry Talk
 430 Announcements
 4.4K Ideas & Feature Requests
 137 Brandfolder
 129 Just for fun
 128 Community Job Board
 446 Show & Tell
 28 Member Spotlight
 1 SmartStories
 284 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!