The syntax for OFFSET, as well as for all other Excel functions (except
DATEDIF) is available in Excel help.

If you want to change the M1 reference two rows at a time, I would suggest
you use OFFSET again, perhaps something along the lines of
OFFSET(M$1,INT((ROW()-ROW(M$1))/2),0)
--
David Biddulph

"MM Lines" wrote in message
...
Thanks again Carlos...that was perfect. I'm curious though. I understand
the formula perfectly except for the "0" in the OFFSET portion. What is
that
zero signifying? I assume the "2" is becuase I need to have the cell two
over from my target analyzed.

As a caveat to this whole thing, I have the formula now entered in for
about
60 rows with $'s in front of the cell labels. Two copy and paste this
easily, is there a way to just change portions of formula in mass
quantities?
In other words if I want to change your M1 to M3 the next two rows and
then
M5 for the following two rows is there some kind of paste special for just
that part of the formula?

"carlo" wrote:

Hi MM Lines

I am not quite sure, if i understand you exactly, but this works for
me:

=SUMPRODUCT(((A1:F30)=M1)*((OFFSET(A1:F30,0,2))="Y ES"))

Your Target cell should not be in the Range you search, that's why i
new changed it to M1.

Hope that makes sense, otherwise just ask

Carlo