Forum Discussion
Sum range of cells based on a common value in a column
Hi there,
Bit of a novice excel user here. I feel like what I'm trying to do is very possible but I'm not sure how to make it happen. I manage meeting rooms in an office tower and am tracking issues that arise in these rooms. My spreadsheet consists of:
columnA: floor number
columnB: room number
columns C, D, E: each respective column for a different recurring issue
The idea is that when an issue is discovered, a value of 1 is added to the appropriate column in the row for that particular room. I then want to sum all of the values for a given floor, to get the total issues on a floor. I can do this by manually selecting ranges using the sum function, but I would have to do this function 50 times (once per floor).
In my mind I want excel to do the following:
IF the value in column A is "5", THEN add the sum of columns C:E in that row, with all the other rows that also have the value "5" in column A
Thoughts?
=SUMPRODUCT(($A$2:$A$25=G7)*$C$2:$E$25)You can try this formula for the data layout of the example.
