How to sum a number from a range of cells, based on value

Solved/Closed
cathcam Posts 2 Registration date Saturday September 30, 2017 Status Member Last seen October 2, 2017 - Sep 30, 2017 at 03:28 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Oct 5, 2017 at 11:07 AM
Hello,

I'm trying to update a spreadsheet that contains our wedding mailing list.

The rows contain the invitee name, address and email address.

In Column N we have the word Ceremony if they've been invited to the ceremony
In Column O if they've RSVP'd we have the number of attendees

We are assuming everyone attending the ceremony will also attend the reception.

I have a cell that does a simple add to get the number of people that have RSVP'd which gives us the number that we expect to attend the reception. =sum(O2:O204)

I'm stuck on just the number that have RSVP'd to the ceremony. I have tried numerous... but don't really now what I'm doing...

=SUM(IF(N2:N204="ceremony",O2:O204))

I can get true or false for a single cell...
=SUMPRODUCT(--ISNUMBER(SEARCH({"ceremony"},N5)))>0

Help?


1 response

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Updated on Oct 2, 2017 at 12:08 PM
Hi Cathcam ,

You can either change the formula you have
=SUM(IF(N2:N204="ceremony",O2:O204))
into an array formula by hitting Ctrl+Shift+Enter instead of just enter. Doing this will enclose your formula in curly brackets.

Or you can use the COUNTIF function:
=COUNTIF(N2:N204,"ceremony",O2:O204)

Best of luck with the wedding :-)

Best regards,
Trowa


Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.
cathcam Posts 2 Registration date Saturday September 30, 2017 Status Member Last seen October 2, 2017
Oct 2, 2017 at 07:26 PM
Ha! Great, I needed Sumif, not Countif. That gave me enough to go look again.
=sumif(N:N,"ceremony",O:O)

FYI, COUNTIF only take 2 arguments, so it tells me how many acceptances.

Thank you so much for getting me on the right path.
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Oct 5, 2017 at 11:07 AM
You are right, my bad.

Good to see you worked it out.