# sum if and match range of cells in

edited 01/06/23

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.

Tags:

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

• Employee

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 per-value, 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