Report

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

Ask a question cathcam 2Posts Saturday September 30, 2017Registration date October 2, 2017 Last seen - Last answered on Oct 5, 2017 at 11:07 AM by TrowaD
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?


Helpful
+0
plus moins
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


cathcam 2Posts 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.
Reply
TrowaD 2280Posts Sunday September 12, 2010Registration date ModeratorStatus October 16, 2017 Last seen - Oct 5, 2017 at 11:07 AM
You are right, my bad.

Good to see you worked it out.
Reply
Leave a comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!