How to sum a number from a range of cells, based on value [Solved/Closed]

Report
Posts
2
Registration date
Saturday September 30, 2017
Status
Member
Last seen
October 2, 2017
-
TrowaD
Posts
2576
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 10, 2019
-
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?


Posts
2576
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 10, 2019
383
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

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
2576
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 10, 2019
383
You are right, my bad.

Good to see you worked it out.