Hi all,

I've created a spreadsheet to track a guest list and RSVPs. I want to account for the four possible scenarios for RSVPs: #guests=#expected, #guests>#expected, #guests<#expected, and no RSVP has been returned.

B C D E
Expected Total Adults Kids
6 6 2 4
2 0
2 3 2 1
3 2 2 0
2 1 1 0

Column C=D+E
I've applied conditional formatting so that cells in column B turn red if #guests<#expected using =$D5>0 and cells turn green if guests have replied that they are attending (regardless of the # of guests) using =AND(ISNUMBER($D5),$D5=0). Any guests who have not replied have column C blank.

I want to calculate a comparison between how many guests were expected and how many are actually attending, based on the RSVPs received so far. In another cell, I can get =IF($D$5=0,"",$C$5-$B$5) to correctly display 0. In this short example, I would expect a sum of 3.
How can I extend this formula over range $D$5:$D$300?

Thanks much!
Kristi