I have searched through this forum and thought I had found my answer, but I
get #NUM returned - Excel 2003

Formula I have is this:

=SUMPRODUCT(--(TravelArranger=A4),--(TranType="ORIGINAL"),--(Class="Economy"),--(Online="OffLine"))

The data is coming from another sheet in the workbook:
I have setup named ranges in my workbook:
What I am trying to achieve is:
The number of bookings made throughout a month with the following criteria
If the Travel Arranger = A4 (name in A4) AND the TranType = "ORIGINAL" AND
the Class = "Economy" and the Online = "Offline"
I want the number of times this has occurred. I did have another field for
this to take the count from but gave up on that.

Thanks in advance

Anne

Do you have #NUM! errors in *any* of your ranges?

Are your ranges dynamic ranges? If so, are you sure they are being
calculated correctly?

Biff

"enna49" wrote in message
...
I have searched through this forum and thought I had found my answer, but I
get #NUM returned - Excel 2003

Formula I have is this:

=SUMPRODUCT(--(TravelArranger=A4),--(TranType="ORIGINAL"),--(Class="Economy"),--(Online="OffLine"))

The data is coming from another sheet in the workbook:
I have setup named ranges in my workbook:
What I am trying to achieve is:
The number of bookings made throughout a month with the following criteria
If the Travel Arranger = A4 (name in A4) AND the TranType = "ORIGINAL" AND
the Class = "Economy" and the Online = "Offline"
I want the number of times this has occurred. I did have another field
for
this to take the count from but gave up on that.

Thanks in advance

Anne

Hi

No there is no #NUM in any ranges, but have found my error. One of the
ranges was set to a different no of rows. Sorry, once I set off the query
and walked away for a while and had yet another check, I found the error.
Changed this and all works perfectly thanks.
Can I now ask another question, I want to SUM according to this exact
criteria but use a another column called "PaidFare"

I thought just adding the field would work. Please can you confirm if this
is correct.

=SUMPRODUCT(--(TravelArranger=A4),--(TranType="ORIGINAL"),--(Class="Economy"),--(Online="OffLine"),PaidFare)
Thanks
Anne

"T. Valko" wrote:

Do you have #NUM! errors in *any* of your ranges?

Are your ranges dynamic ranges? If so, are you sure they are being
calculated correctly?

Biff

"enna49" wrote in message
...
I have searched through this forum and thought I had found my answer, but I
get #NUM returned - Excel 2003

Formula I have is this:

=SUMPRODUCT(--(TravelArranger=A4),--(TranType="ORIGINAL"),--(Class="Economy"),--(Online="OffLine"))

The data is coming from another sheet in the workbook:
I have setup named ranges in my workbook:
What I am trying to achieve is:
The number of bookings made throughout a month with the following criteria
If the Travel Arranger = A4 (name in A4) AND the TranType = "ORIGINAL" AND
the Class = "Economy" and the Online = "Offline"
I want the number of times this has occurred. I did have another field
for
this to take the count from but gave up on that.

Thanks in advance

Anne

You get the right Syntax. I think, it should work


"enna49" wrote:

Hi

No there is no #NUM in any ranges, but have found my error. One of the
ranges was set to a different no of rows. Sorry, once I set off the query
and walked away for a while and had yet another check, I found the error.
Changed this and all works perfectly thanks.
Can I now ask another question, I want to SUM according to this exact
criteria but use a another column called "PaidFare"

I thought just adding the field would work. Please can you confirm if this
is correct.

=SUMPRODUCT(--(TravelArranger=A4),--(TranType="ORIGINAL"),--(Class="Economy"),--(Online="OffLine"),PaidFare)
Thanks
Anne

"T. Valko" wrote:

Do you have #NUM! errors in *any* of your ranges?

Are your ranges dynamic ranges? If so, are you sure they are being
calculated correctly?

Biff

"enna49" wrote in message
...
I have searched through this forum and thought I had found my answer, but I
get #NUM returned - Excel 2003

Formula I have is this:

=SUMPRODUCT(--(TravelArranger=A4),--(TranType="ORIGINAL"),--(Class="Economy"),--(Online="OffLine"))

The data is coming from another sheet in the workbook:
I have setup named ranges in my workbook:
What I am trying to achieve is:
The number of bookings made throughout a month with the following criteria
If the Travel Arranger = A4 (name in A4) AND the TranType = "ORIGINAL" AND
the Class = "Economy" and the Online = "Offline"
I want the number of times this has occurred. I did have another field
for
this to take the count from but gave up on that.

Thanks in advance

Anne

"enna49" wrote...
No there is no #NUM in any ranges, but have found my error. One of the
ranges was set to a different no of rows. . . .
....

Then you would have had a #N/A error rather than a #NUM! error. Accuracy in
what YOU post affects the answers you'll receive.