Count number of nonblank cells in column B, C if value in column D = 1 (or 2)

Hello,

I currently have a guest list made in Excel. Column B is "Invitee" and Column C is "Guest". The priority of these guests is in Column D. The priority has a value of 1, 2 or 3.

I am looking to make a function to count the number of guests that are priority 1, or 2 or 3. The only hang-up I'm having is that some of the Invitees have blank cells in the guest column, so I can't just use =COUNTIF(Table4[Priority];1) * 2

I have tried using IF, CountIF and CountA but I can't seem to use them properly in conjunction to show totals. Is anyone able to offer some advice?

Thank you in advance!

Hi,

I have tried using COUNTIFS(), but it has returned #VALUE!. I tried =COUNTIFS(Table4[Priority];1; Table4[[Invitee]:[Guest]]; "<>")

I have uploaded a screenshot of what I am hoping to do...
I would like the TOTAL P1 to give the number of guests (dates included) with priority 1, P2 to give guests with priority 2 and so on.


Excel Q.PNG

Hello,

I have attached the sample excel file, showing the values I am hoping to get for TOTAL P1, TOTAL P2 and TOTAL P3.

Thanks for your time.

I would like it to add up the total of non-blank spaces from Invitee + Guest columns. Most invitees will also have a guest, but not all. I am trying to show how many total people to account for that are P1, P2 and P3, including their guests.
Sorry if this is confusing. It is a wedding guest list, if that helps with the concept.

Hi,
Thanks for the reply.

This is getting close to what I am looking for, except I want to count the number of total guests from both column B AND column C.

Formula:

Please Login or Register  to view this content.

but when I try to change to both B and C columns, I get a VALUE! error.

Thanks Richard!
I don't know why I didn't think to add two CountIFS together ... D'oh!