Using Microsoft Excel for Mac, via Office 360 subscription/ Excel '16 for Mac
(crossposted on Stack Overflow)
BACKGROUND and DATA:
I’m planning a weekend conference with several events. Caterers and others want estimates well in advance of when we will get actual numbers.
In some cases, I can guess attendance based on the invitee. In others, I can’t. So I set up a conditional formula that color codes whether or not I’ve received an official RSVP.
But this doesn’t help me quickly scan and use my data.
PROBLEM:
I'm hoping to find a way to add an additional IF formula that uses the Y/N response from the “RSVP REC'd” in Column A to create a special row of totals at the bottom, that I would label “ESTIMATED TOTAL” or some such.
I’d like to keep my RSVP Y/N color coding because it helps me keep track of who has and hasn’t responded.
PROCESS AND DESIRED OUTCOME:
What I need is to take anything in pink — which means that if there is a value, it is an estimate — automatically add it to any other pink estimated numbers, and then add it to any black, actual numbers to give me a row at the bottom of estimated attendance. PINK BOXED ROW 14, above. That row of totals will change frequently, as my estimates are replaced by real responses. Those are easy to see, since as soon as I indicate in Column A that I have received an RSVP, the row changes to black. Yay for conditional formatting.
However, for the black numbers, which indicate that I have an RSVP and therefore that the numbers are confirmed, I want to automatically add these together without the pink, estimated numbers, so that I have a row of actual, confirmed responses at the very bottom. BLACK BOXED ROW 16, above.
(The highlighted numbers at the bottom, ROW 17, in red, are a repeat of the actual totals for the biggest event, on Saturday, with numbers of attendees and their children added together.)
I have tried various formulas I’ve found online, but they either fail completely, or fail to exclude the pink, estimated numbers for my real, confirmed totals.
Thanks in advance for any thoughts!