SUM(AND(ISBLANK formula question

Libbsta_Of_the_Land

This is my first post on this forum, though I have been using it for years. I appreciate all the brilliant answers that have helped me.

I am having an issue with the ISBLANK function. We have a guest list that took on a mind of it's own after guest numbers on different forms were modified depending on which executives were requesting extra guests, and I am trying to get a more accurate guest count. I am trying to return the value "2" if the 2 [extra guests] columns are blank and two other columns are marked "yes". If either of the [extra guests] columns have a value, then it should return the value "1". From there, I add that number (either 1 or 2, depending on the criteria) together, so I can get a total guest count. I tried to start simple and add in one ISBLANK function into my formula that was already working (except for the issue of adding another guest if the [extra guests] columns were not blank, which I didn't want). But I can't even seem to add in one ISBLANK condition without me getting an error. This is what I am aiming for:

=SUM(IF(AND(ISBLANK([Extra Guests]@row), ISBLANK([Sterling Extra Guest Numbers]@row, [Attending?]@row = "Yes", [Guest?]@row = "Yes", 2)), IF(AND([Attending?]@row = "Yes", [Guest?]@row = "No"), 1, 0)) + [Extra Guest Numbers]@row + [Sterling Extra Guest Numbers]@row)

This is what was working:

=SUM(IF(AND([Attending?]@row = "Yes", [Guest?]@row = "Yes"), 2, IF(AND([Attending?]@row = "Yes", [Guest?]@row = "No"), 1, 0)) + [Extra Guest Numbers]@row + [Sterling Extra Guest Numbers]@row)

Any help would be greatly appreciated. Thanks!!!

Paul Newcome

Are you able to provide some screenshots with sample data for reference?

Libbsta_Of_the_Land

Certainly!

As you can see, I had to add extra guest columns. Originally, one invitee could only bring one guest, but after we had distributed the form, some executives requested more. The "Sterling" executive could invite more extra guests than the other executives, which is why I had to add another column for a form specific to this executive. The issue is, I was originally counting each "yes" in the "attending?" and "Guest?" columns as one guest, but if one of the executives' guests fills in extra guest numbers, I need to NOT count the "guest?" column. Therefore, I was trying to use the ISBLANK function with both the "extra guest numbers" and Sterling extra guest numbers" as a condition to count both the "attending?" and "guest?" "yes" as a guest. Otherwise, if the extra guest number columns are not blank, ONLY the "attending" "yes" should count as one guest.

Libbsta_Of_the_Land

So, essentially, the "Guest Count" should be 7 and not 8, because it shouldn't count the "Guest?" column because the two extra guest columns have been filled.

Libbsta_Of_the_Land

I figured a workaround:

=SUM(IF(OR([Attending?]@row = "Yes", [Guest?]@row = "Yes"), 2), IF(AND([Attending?]@row = "Yes", [Guest?]@row = "No"), 1), IF(OR([Extra Guest Numbers]@row > 0, [Sterling Extra Guest Numbers]@row > 0), -1, 0)) + [Extra Guest Numbers]@row + [Sterling Extra Guest Numbers]@row

Libbsta_Of_the_Land

Actually, it didn't work. It almost did... 🤣

Paul Newcome

Try this:

=IF([Attending]@row = "Yes", 1, 0) + [Extra Guest Numbers]@row + [Sterling Extra Guest Numbers]@row) + IF([Extra Guest Numbers]@row + [Sterling Extra Guest Numbers]@row = 0, 1, 0)