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

cathcam 2 Posts Saturday September 30, 2017Registration date October 2, 2017 Last seen - Sep 30, 2017 at 03:28 PM - Latest reply: TrowaD 2355 Posts Sunday September 12, 2010Registration dateModeratorStatus May 17, 2018 Last seen
- 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?


See more 

Your reply

3 replies

TrowaD 2355 Posts Sunday September 12, 2010Registration dateModeratorStatus May 17, 2018 Last seen - Updated by TrowaD on 2/10/17 at 12:08 PM
0
Helpful
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 2 Posts Saturday September 30, 2017Registration date October 2, 2017 Last seen - 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 2355 Posts Sunday September 12, 2010Registration dateModeratorStatus May 17, 2018 Last seen - Oct 5, 2017 at 11:07 AM
You are right, my bad.

Good to see you worked it out.
Respond to TrowaD