COUNTIF with a twist ;)

[d365b]

Hello everyone!

I hope you are well. I am trying to work out a formula but I just can't get my head around this.

Basically, I have a table to list people seated around an actual table and I have to work out the number of people around the table. In column C the user will either put the family name (e.g. "Browns", as in the attached example) or the name of the person (e.g. "John Smith", as in the attached example). Now the user will have to specify how many family members will be seated at the table in column H (e.g. 5, as in the attached example) but there is no need to specify John Smith is only one person.

The formula I am trying to build in H15 is:

IF C5 and H5 are not blank THEN increase the count in H15 by the amount in H5
ELSEIF C5 is not blank and H5 is blank THEN increase the count in H15 by 1

Does anyone have come across to something similar?

Many thanks,
Dani

[TMS]

One way

Please Login or Register  to view this content.

[d365b]

Awesome!

I have slightly modified as follow

Please Login or Register  to view this content.

so if there is a number but not a family name it will not increment by that amount

[TMS]

You're welcome.


You are entitled to amend the formula to your heart's content. Whatever works for you. I just worked with the parameters you outlined.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.