sun product issue

[jw01]

Hello

Im having a hard time with the sum product rule. im not sure at all why its giving me an error.

here is my formula:

=SUMPRODUCT(--('NAR Performance'!$D$14:$D$185=Sheet1!C7)*--('NAR Performance'!$H$146:$H$185=Sheet1!A9))

where range D = SE, SSE
C7 = SE

where range H = country i.e. CA, US
A9 = CA

it's giving me NA error.

can someone pls help me out. I have highlighted it in red on the attached worksheet. thx you so much and pls!

basically, im trying to count how many of sales ppl called "SE or SSE" are located in the states or canada.

[Cutter]

In C9 you have
=SUMPRODUCT(--('NAR Performance'!$D$14:$D$185=Sheet1!C7)*--('NAR Performance'!$H$146:$H$185=Sheet1!A9))

Note the mismatched ranges you have $14 in first segment and $146 in the 2nd segment - remove the 6

I would use:

=SUMPRODUCT(('NAR Performance'!$D$14:$D$185=Sheet1!C7)*('NAR Performance'!$H$14:$H$185=Sheet1!A9))


BTW - you should correct the spelling mistake in your title (to assist future searchers)