Previous Topic Next Topic

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

Answer 1 / 1
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.
0
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.
0
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.
0

Similar discussions

help me to add particular cells
mathewmunna -
mathew munna -

2 responses
Fill adjacent cell with number from text answer
MitchP -
vcoolio -

20 responses
Excel function question
1234ah -
ac3mark -

3 responses
If a cell contains any text then add a value
cbrizz -
Pharmanumbers -

20 responses
Return Seq Values Until Blank value then return different set of seq values
lorieb63 -
TrowaD -

1 response