How do I set up an IF formula with multiple qualifiers?
 

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!

How do I set up an IF formula with multiple qualifiers?
 

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!

How do I set up an IF formula with multiple qualifiers?
 

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!

How do I set up an IF formula with multiple qualifiers?
 

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!

How do I set up an IF formula with multiple qualifiers?
 

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!