SUM(AND(ISBLANK formula question
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!!!
Answers

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

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.

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.

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

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

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)
Help Article Resources
Categories
Check out the Formula Handbook template!