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

Thank you for the help and explaining it to me so simply. The formula works perfect.
Help Article Resources
Categories
Check out the Formula Handbook template!