Try this:

=IF(AND(COUNTIF('sheet1'!$A$1:$A$400,"Mike"),SUM(C OUNTIF('sheet1'!$A$1:$AP$1,{"trinket","widget"}))) ,INDEX('sheet1'!$A$1:$AP$400,MATCH("mike",'sheet1' !$A$1:$A$400,0),MATCH("trinket",'sheet1'!$A$1:$AP$ 1,0)))+INDEX('sheet1'!$A$1:$AP$400,MATCH("mike",'s heet1'!$A$1:$A$400,0),MATCH("widget",'sheet1'!$A$1 :$AP$1,0)),"the criteria you're searching is not on the list")




"denise" wrote:

Hi Folks,

The formula listed below works great but now I need to include an "if isna"
statement for those incidents where Mike may not have any trinkets or widgets
to sum. I'm hoping someone can help me re-write this so it there will be
fewer characters and I can fit in the "if isna" statement.

=INDEX('sheet1'!$A$1:$AP$400,MATCH("mike",'sheet1' !$A$1:$A$400,0),MATCH("trinket",'sheet1'!$A$1:$AP$ 1,0)))+INDEX('sheet1'!$A$1:$AP$400,MATCH("mike",'s heet1'!$A$1:$A$400,0),MATCH("widget",'sheet1'!$A$1 :$AP$1,0))

Thanks,
Denise