Attempting a SUMPRODUCT with a count range

[Phlegon_of_Tralles]

I'm not sure if that's the best descriptor, but this is basically a follow up to this post.

The difference is that instead of referencing B1 or C1 or D1 (the Job 1, Job 2, or Job 3 criteria) I'm referencing a range of jobs on tab 'Position' for Job 1, Job 2, Job 3. This was similar to some of my other posts, so I think I have to ditch the countifs that I had been using in favor of a SUMPRODUCT. Unfortunately I don't understand that function as well as I should and when I input what I think would work I get a #VALUE! error.

=SUMPRODUCT(INDIRECT("'"&$K$3&"'!$E:$E"),"<1")*--(ISNUMBER(MATCH(INDIRECT("'"&$K$3&"'!$C:$C"),Position!$A$2:$A$3,0)))

Can someone help me? I think if I get the first one (the <1) I can figure out the rest (the tenure ranges 1-2, 3-5, etc in column A on tab 'Data').

If this can be done in a countif that'd be great, but I don't think it can.

[Phlegon_of_Tralles]

Not much of an update, but I think I might be closer(?).

=SUMPRODUCT(INDIRECT("'"&$K$3&"'!$E:$E")="<1")*--(ISNUMBER(MATCH(INDIRECT("'"&$K$38&"'!$C:$C"),Position!$A$2:$A$3,0)))

The above formula doesn't give me a #VALUE! error...but it doesn't give me the proper count (3).

I'm not sure what I'm missing.

Edit:

I've also tried this, but this doesn't work either.
=COUNTIFS(INDIRECT("'"&$K$3&"'!$C:$C"),{"AAAA","BBBB"},INDIRECT("'"&$K$3&"'!$E:$E"),"<1")

[XOR LX]

Hi,

=SUMPRODUCT(COUNTIFS(INDIRECT("'"&$K$3&"'!E:E"),"<1",INDIRECT("'"&$K$3&"'!C:C"),Position!$A$2:$A$3))

Regards

[Phlegon_of_Tralles]

You sir or madam, are a genius.

Actually this forum is filled with geniuses. I'm amazed.

[XOR LX]

High praise indeed!

You're very welcome!

Cheers