Sum Product for does not match?

[Daniellel]

Hi,

I need a way of doing a sum product for a non match.

i.e.

=((SUMPRODUCT(--('Sheet1'!$N:$N="not Yes"),--('Sheet1'!$N:$N="not No"),--('Sheet1'!$N:$N="not Sometimes"))))

The cells in column "N" can say up to 25 different statements but i want to tell it what NOT to count.

 

[Cindy Ellis]

Does this work for you?

=SUMPRODUCT(--('Sheet1'!$N:$N<>"Yes"),--('Sheet1'!$N:$N<>"No"),--('Sheet1'!$N:$N<>"Sometimes"))

Cindy

 

[njimack]

This approach should work for you...
=SUMPRODUCT(--(ISERROR(MATCH($N:$N,{"Yes","No","Sometimes"},0))))

 

[Daniellel]

Does this work for you?

=SUMPRODUCT(--('Sheet1'!$N:$N<>"Yes"),--('Sheet1'!$N:$N<>"No"),--('Sheet1'!$N:$N<>"Sometimes"))

Cindy

This works great, but if i want it to tell me how many 'Yes' 'No' and 'Sometimes' there are all together as a whole number???

 

[Cindy Ellis]

So...what do you want the result to look like? Do you want all 3 answers (not "Yes", Not "No", and Not "Sometimes") all in the same cell?
Cindy

 

[njimack]

Can you post a sample of your data with expected result?