Way to create an IF formula for automatic estimated totals in Excel for Mac?

Question

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.

Spreadsheet sample

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.

Conditional rules screenshot

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!

Answer 1

@SolarMike is correct, you need SUMIF() to do what you're asking.

I wondered whether SUMIF() would have a problem with the blank and text values in some of the cells, but it just ignores them and sums the appropriate numbers correctly.

I've reproduced part of your data table below, and included rows for the estimated total attendance and the actual number confirmed. The former is just the sum of all the numbers in the column:

=SUM(B2:B8)

and the latter uses SUMIF():

=SUMIF($A2:$A8,"Y",B2:B8)

How it works: SUMIF() looks in A2:A8 for the cells that contain "Y", and sums the corresponding numbers from B2:B8. As the formula is filled to the right, it sums the numbers in each successive column.

Hope this helps, and good luck.

Answer 2

Sorry I’m on a smartphone so can’t test my suggestion...

You can use sumifs() to sum the numbers if the condition rsvp is met : ie sum all the numbers which have rsvp in the associated cell. Without using if()...

Just for info, there are several functions that work like this : countifs, averageifs ... work looking for.