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

Question

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?


System Configuration: Windows / Chrome 60.0.3112.113

TrowaD · Answer

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.

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

1 response

Similar discussions

Subscribe To Our Newsletter!