Need help in Excel SUMIFS + MATCH

[nittia]

I want to calculate the total rent for the complex where at least one room is occupied.

i.e for example the condition would be Apartment Complex, say "Mountaintop View" from Summary Statistics is present in Apartment complex of "List of Rental Property" and also if at least one room is occupied (MATCH("Yes",F14:F26,0) = 1 ) then I need total rent to be calculated. In the given example it would be 3025(875+950+1200).

I have tried using SUMIFS + index + Match. however i didn't get the output i need, May be because i am using the wrong method.

I want this to be calculated and placed in 'potential rent'. and would like to be calculated for all the complex in the 'Summary Statistics' table.

Need help in this issue.

[6StringJazzer]

I think you just need a plain vanilla SUMIF. Put this in F6 and then copy down. You will have to change the ranges to include the largest possible number of rows under "List of Rental Property."

=SUMIF($B$14:$B$26,A6,$E$14:$E$26)

[nittia]

I think you just need a plain vanilla SUMIF. Put this in F6 and then copy down. You will have to change the ranges to include the largest possible number of rows under "List of Rental Property."

=SUMIF($B$14:$B$26,A6,$E$14:$E$26)

This formula sum up rent even if no room is occupied. ex. For Lakeview Apartments, The potential rent is supposed to be 0 as no room is occupied, where as this formula would give us 1750.

[6StringJazzer]

That adds some clarification.

=SUMIF($B$14:$B$26,A6,$E$14:$E$26)*--(COUNTIFS($B$14:$B$26,A6,$F$14:$F$26,"Yes")>0)

For each property, this formula calculates the sum of all units if any unit is occupied. If no unit is occupied, it returns 0.

[nittia]

That adds some clarification.

=SUMIF($B$14:$B$26,A6,$E$14:$E$26)*--(COUNTIFS($B$14:$B$26,A6,$F$14:$F$26,"Yes")>0)

For each property, this formula calculates the sum of all units if any unit is occupied. If no unit is occupied, it returns 0.

Thank you. It works.

[sabrinigreen]

My issue is very similar so I am posting here instead of a new thread.

I am setting up a monthly summary and instead of manually setting ranges I want it to add up the cells if the month equals that month. Example:
Add up all the scheduled hours (column C) if the month (column B) is 10 (for October)

Also, in another sheet, the date is set up 10/22/11 - so sumif the month is 10 - how would I do that?

Thank you.

[royUK]

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.