Sheet Summary Formula Help

Natalie Gorman

Hello

I am trying to summarise a contact list column, however the problem is that I have a mixture of name and email address (a common problem with this property type it seems).

To ensure that I have captured both occurrences, I have tried the formula below, but I get #invalid data type.

Is someone able to advise where I am going wrong?

=COUNTIFS(OR(Responsible:Responsible, "Natalie Gorman", Responsible:Responsible, "natalie.gorman@rafcte.com"), Status:Status, <>"Blue")

Many thanks

Natalie Gorman

Kaveri Vipat

Hi Natalie Gorman, 

You can try this formula- I have created a helper column 'H1'. So, if there is the name or email address of "Natalie" and the status is not blue then it will show "1" and then I have applied a formula in the sheet summary to count the 'H1'. This is how we will get the required count. 

H1 column formula is: =IF(AND(Responsible@row = "Natalie Gorman", Status@row <> "Blue"), 1, IF(AND(Responsible@row = "natalie.gorman@rafcte.com", Status@row <> "Blue"), 1, "")) 

Sheet summary formula is : =COUNT([H1]:[H1]) 

Natalie Gorman

Hi Kaveri

Thank you for your response.

Unfortunately I don't think this will work for me as I have a number of people I am looking to summarise for in the sheet summary tool.

Kind regards,

Natalie Gorman