Count and sum excluding a range of cells

[elk03]

Hi, I am trying to count and sum cells in a range but excluding a long list of names, and exclude blank cells. I am having trouble creating a formula that will work though. I have a big data set based on location of state. I am looking to count and sum those cells that do not have a State listed. They have other random names listed instead. Thanks.

 

[jasonb75]

Basic principle of the formula, without seeing an example of the sheet layout this is the best I can offer.
Sum

=SUMPRODUCT(--ISNA(MATCH(name list, list to exclude, FALSE)),List to sum)

count

=SUMPRODUCT(--ISNA(MATCH(name list, list to exclude, FALSE)))

 

[elk03]

Thank you very much, that seemed to work. It does count blank cells though. I think I can work around that, but is there a simple way to also exclude blank cells?

 

[jasonb75]

Oops, I forgot to allow for that.

=SUMPRODUCT(--ISNA(MATCH(name list, list to exclude, FALSE)),--(name list<>""),List to sum)

 

[elk03]

Thanks for your help.