SUMPRODUCT query
 

I'm sure I must be doing something stupid here ...

I have a very simple database amongst which are 4 columns:

- D: the names of training courses to be held
- G: the dates being held
- H: the course venues
- J: whether the delegate has accepted the invitation to attend ("Y" or "N")

I have named each of these ranges using rows 2 - 200 in each case.

I need a summary table showing, for each course, how many have accepted.

My summary table simply has 4 columns:

- Course (A)
- Date (B)
- Venue (C)
- Acceptance (D)

In cell D2 of the summary table I have:

=SUMPRODUCT(--(Course=$A2)*(Date=$B2)*(Venue=$C2)*(Acceptance="Y "))



but this returns the '#N/A' error message. I can't see anything that might
be causing this (ie; mis-spellings, etc) but it seems that adding-in the
Venue parameter causes the problem (ie; it works fine with just the other 3
variables). But, if I just trim the whole function down to



=SUMPRODUCT(--(Venue=$C2))



that returns the correct result!



Any ideas?



Many thanks.

SUMPRODUCT query
 

Terry,

The formula is fine so it must be the named ranges or the data. Are you sure
the ranges are the same size.?

A common pitfall when looking up text is erroneous spaces, have you checked?

Mike

"Terry Bennett" wrote:

I'm sure I must be doing something stupid here ...

I have a very simple database amongst which are 4 columns:

- D: the names of training courses to be held
- G: the dates being held
- H: the course venues
- J: whether the delegate has accepted the invitation to attend ("Y" or "N")

I have named each of these ranges using rows 2 - 200 in each case.

I need a summary table showing, for each course, how many have accepted.

My summary table simply has 4 columns:

- Course (A)
- Date (B)
- Venue (C)
- Acceptance (D)

In cell D2 of the summary table I have:

=SUMPRODUCT(--(Course=$A2)*(Date=$B2)*(Venue=$C2)*(Acceptance="Y "))



but this returns the '#N/A' error message. I can't see anything that might
be causing this (ie; mis-spellings, etc) but it seems that adding-in the
Venue parameter causes the problem (ie; it works fine with just the other 3
variables). But, if I just trim the whole function down to



=SUMPRODUCT(--(Venue=$C2))



that returns the correct result!



Any ideas?



Many thanks.

SUMPRODUCT query
 

Functionally, everything look right. Based on the error, I'd double check the
size of you "Venue" range, make sure you didn't accidentally select rows
2:201 (thus producing unequal arrays in the 1st equation, but still working
in smaller equation).

You error checking definitely seems to have rulled out spelling errors as
well, so I'm afraid that a array sizing problem is the only thing I can think
of.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Terry Bennett" wrote:

I'm sure I must be doing something stupid here ...

I have a very simple database amongst which are 4 columns:

- D: the names of training courses to be held
- G: the dates being held
- H: the course venues
- J: whether the delegate has accepted the invitation to attend ("Y" or "N")

I have named each of these ranges using rows 2 - 200 in each case.

I need a summary table showing, for each course, how many have accepted.

My summary table simply has 4 columns:

- Course (A)
- Date (B)
- Venue (C)
- Acceptance (D)

In cell D2 of the summary table I have:

=SUMPRODUCT(--(Course=$A2)*(Date=$B2)*(Venue=$C2)*(Acceptance="Y "))



but this returns the '#N/A' error message. I can't see anything that might
be causing this (ie; mis-spellings, etc) but it seems that adding-in the
Venue parameter causes the problem (ie; it works fine with just the other 3
variables). But, if I just trim the whole function down to



=SUMPRODUCT(--(Venue=$C2))



that returns the correct result!



Any ideas?



Many thanks.

SUMPRODUCT query
 

Thanks guys - I will go back and double-check everything again.


"Mike H" wrote in message
...
Terry,

The formula is fine so it must be the named ranges or the data. Are you
sure
the ranges are the same size.?

A common pitfall when looking up text is erroneous spaces, have you
checked?

Mike

"Terry Bennett" wrote:

I'm sure I must be doing something stupid here ...

I have a very simple database amongst which are 4 columns:

- D: the names of training courses to be held
- G: the dates being held
- H: the course venues
- J: whether the delegate has accepted the invitation to attend ("Y" or
"N")

I have named each of these ranges using rows 2 - 200 in each case.

I need a summary table showing, for each course, how many have accepted.

My summary table simply has 4 columns:

- Course (A)
- Date (B)
- Venue (C)
- Acceptance (D)

In cell D2 of the summary table I have:

=SUMPRODUCT(--(Course=$A2)*(Date=$B2)*(Venue=$C2)*(Acceptance="Y "))



but this returns the '#N/A' error message. I can't see anything that
might
be causing this (ie; mis-spellings, etc) but it seems that adding-in the
Venue parameter causes the problem (ie; it works fine with just the other
3
variables). But, if I just trim the whole function down to



=SUMPRODUCT(--(Venue=$C2))



that returns the correct result!



Any ideas?



Many thanks.