Counting Products Sold Per Rep

I'm trying to write a formula to count under certain conditions. I tried the following (and some other variations), but it doesn't work, it comes back #VALUE!:

=COUNTIFS('[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$K$10:$K$100,D7,'[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$A:$A,A34)

I need to count the number of products a rep sold in one day. He can have several sales in one day, and also sell more than one product per sale. The list I am pulling from has all sales from all reps for the day.

Any suggestions on how to make this work?

Thanks! I wasn't familar with SUMPRODUCT. I had to modify it to encompass all the product fields, and then I put it into an IF funtion so that if they didn't sell anything it'll return a blank result. It looks really long and gaudy, but it works!

=IF(SUMPRODUCT((--('[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$K$10:$K$100='[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$K$7)--('[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$K$10:$K$100='[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$K$9)--('[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$L$10:$L$100='[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$L$6)--('[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$L$10:$L$100='[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$L$7)--('[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$L$10:$L$100='[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$L$8)--('[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$L$10:$L$100='[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$L$9)--('[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$M$10:$M$100='[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$M$6)--('[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$M$10:$M$100='[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$M$7)--('[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$M$10:$M$100='[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$M$8)--('[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$M$10:$M$100='[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$M$9)--('[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$N$10:$N$100='[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$N$6)--('[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$N$10:$N$100='[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$N$7)),--('[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$A$10:$A$100=A34))=0,"",SUMPRODUCT((--('[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$K$10:$K$100='[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$K$7)--('[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$K$10:$K$100='[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$K$9)--('[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$L$10:$L$100='[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$L$6)--('[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$L$10:$L$100='[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$L$7)--('[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$L$10:$L$100='[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$L$8)--('[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$L$10:$L$100='[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$L$9)--('[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$M$10:$M$100='[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$M$6)--('[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$M$10:$M$100='[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$M$7)--('[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$M$10:$M$100='[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$M$8)--('[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$M$10:$M$100='[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$M$9)--('[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$N$10:$N$100='[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$N$6)--('[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$N$10:$N$100='[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$N$7)),--('[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$A$10:$A$100=A34)))

Any suggestions on how to shorten it up? If not, at least it works.

I tried it, but I get an error that points to the area between the smiley's, and I can't get past that.

=IFERROR(SUMPRODUCT(--('[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$K$10:$K$100={'[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$K$7),'[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$K$9)}),--('[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$L$10:$L$100={'[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$L$6),'[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$L$7),'[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$L$8),'[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$L$9)},--('[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$M$10:$M$100={'[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$M$6),'[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$M$7),'[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$M$8),'[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$M$9)}--('[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$N$10:$N$100={'[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$N$6),'[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$N$7}),--('[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$A$10:$A$100=A34))),"")

That works, but then if they didn't sell anything, it returns 0 instead of leaving the cell blank.

Does that mean I have to stick with my gaudy formula?

Yes, but it still comes back as 0.

=IFERROR(SUMPRODUCT((--('[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$K$10:$K$100='[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$K$7)--('[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$K$10:$K$100='[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$K$9)--('[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$L$10:$L$100='[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$L$6)--('[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$L$10:$L$100='[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$L$7)--('[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$L$10:$L$100='[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$L$8)--('[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$L$10:$L$100='[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$L$9)--('[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$M$10:$M$100='[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$M$6)--('[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$M$10:$M$100='[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$M$7)--('[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$M$10:$M$100='[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$M$8)--('[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$M$10:$M$100='[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$M$9)--('[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$N$10:$N$100='[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$N$6)--('[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$N$10:$N$100='[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$N$7)),--('[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$A$10:$A$100=A23)),"")

Did I enter something wrong?

It still comes back as 0. I guess I'll just stick with the long formula.

Thanks for all of your help!