thanks however im still unsure as to how to use this function:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(A2,ROW(A2:A100)-ROW(A2),0)),(A2:A100="x")+0)

the 3 is the counting part but im not sure about what the rest means and what its used for....

with the formula listed in my first post i can change:

A2:A100="x"
to
Sheet3!AG:AG=A13

but other then that im pretty lost as to what to do to change this to fit my needs...