Sum range of cells based on a common value in a column

Occasional Visitor

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?

 

 

1 Reply

@Zblahout 

=SUMPRODUCT(($A$2:$A$25=G7)*$C$2:$E$25)

You can try this formula for the data layout of the example.

sumproduct.JPG