I want to see how many people attended an event and then attribute it to that
specific sales person. In the raw data, the attended column is Y or N and
there is a column for the assigned sales guy. Here is my code that is not
working:

=SUM(IF((AND(F:F="Tommy Wheeler",'2007 regs '!G:G="Y")),1,0))

This output should tell me how many people registered within Tommy's sales
territory. Any help would be appreciated!

Try:
With Tommy Wheeler in Cell D1 - in cell E1 enter

=SUMPRODUCT(--(F2:F1000=D1),--(G2:G1000="Y")) ' Sumproduct prior to 2007
does not allow full column Reference (as you have used)

HTH




in article , chubbs at
wrote on 9/10/08 10:51 AM:

I want to see how many people attended an event and then attribute it to that
specific sales person. In the raw data, the attended column is Y or N and
there is a column for the assigned sales guy. Here is my code that is not
working:

=SUM(IF((AND(F:F="Tommy Wheeler",'2007 regs '!G:G="Y")),1,0))

This output should tell me how many people registered within Tommy's sales

territory. Any help would be appreciated!

Hi

you have too many parentheses' It should be
=SUM(IF(AND(F:F="Tommy Wheeler",'2007 regs '!G:G="Y"),1,0))

It also needs to be array entered. To Array enter (or Edit) use
Control+Shift+Enter (CSE) not just Enter.
When you use CSE, Excel will put curly braces around your formula. Do not
type these yourself.
It will appear as below
{=SUM(IF(AND(F:F="Tommy Wheeler",'2007 regs '!G:G="Y"),1,0))}

--
Regards
Roger Govier

"chubbs" wrote in message
...
I want to see how many people attended an event and then attribute it to
that
specific sales person. In the raw data, the attended column is Y or N and
there is a column for the assigned sales guy. Here is my code that is not
working:

=SUM(IF((AND(F:F="Tommy Wheeler",'2007 regs '!G:G="Y")),1,0))

This output should tell me how many people registered within Tommy's sales
territory. Any help would be appreciated!

Roger, when I try that I get a #NUM error. I had come up with a SUMPRODUCT
formula like Jim May's which works, and the following array formula which
works:

{=SUM(IF((F1:F10000="Tommy Wheeler")*(G1:G10000="Y"),1,0))}

But if I change it to whole-column references (using Excel 2003), I get the
#NUM error again:

{=SUM(IF((F:F="Tommy Wheeler")*(G:G="Y"),1,0))}

Am I missing something?

Hutch

"Roger Govier" wrote:

Hi

you have too many parentheses' It should be
=SUM(IF(AND(F:F="Tommy Wheeler",'2007 regs '!G:G="Y"),1,0))

It also needs to be array entered. To Array enter (or Edit) use
Control+Shift+Enter (CSE) not just Enter.
When you use CSE, Excel will put curly braces around your formula. Do not
type these yourself.
It will appear as below
{=SUM(IF(AND(F:F="Tommy Wheeler",'2007 regs '!G:G="Y"),1,0))}

--
Regards
Roger Govier

"chubbs" wrote in message
...
I want to see how many people attended an event and then attribute it to
that
specific sales person. In the raw data, the attended column is Y or N and
there is a column for the assigned sales guy. Here is my code that is not
working:

=SUM(IF((AND(F:F="Tommy Wheeler",'2007 regs '!G:G="Y")),1,0))

This output should tell me how many people registered within Tommy's sales
territory. Any help would be appreciated!

My apologies
My Bad. Not tested
The Sumproduct formula is the way to go.

--
Regards
Roger Govier

"Tom Hutchins" wrote in message
...
Roger, when I try that I get a #NUM error. I had come up with a SUMPRODUCT
formula like Jim May's which works, and the following array formula which
works:

{=SUM(IF((F1:F10000="Tommy Wheeler")*(G1:G10000="Y"),1,0))}

But if I change it to whole-column references (using Excel 2003), I get
the
#NUM error again:

{=SUM(IF((F:F="Tommy Wheeler")*(G:G="Y"),1,0))}

Am I missing something?

Hutch

"Roger Govier" wrote:

Hi

you have too many parentheses' It should be
=SUM(IF(AND(F:F="Tommy Wheeler",'2007 regs '!G:G="Y"),1,0))

It also needs to be array entered. To Array enter (or Edit) use
Control+Shift+Enter (CSE) not just Enter.
When you use CSE, Excel will put curly braces around your formula. Do not
type these yourself.
It will appear as below
{=SUM(IF(AND(F:F="Tommy Wheeler",'2007 regs '!G:G="Y"),1,0))}

--
Regards
Roger Govier

"chubbs" wrote in message
...
I want to see how many people attended an event and then attribute it
to
that
specific sales person. In the raw data, the attended column is Y or N
and
there is a column for the assigned sales guy. Here is my code that is
not
working:

=SUM(IF((AND(F:F="Tommy Wheeler",'2007 regs '!G:G="Y")),1,0))

This output should tell me how many people registered within Tommy's
sales
territory. Any help would be appreciated!