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" <(E-Mail Removed)> wrote in message
news:7B735EE1-5157-4DB5-B98B-(E-Mail Removed)...
>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" <(E-Mail Removed)> wrote in message
> news:7B735EE1-5157-4DB5-B98B-(E-Mail Removed)...
> >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" <(E-Mail Removed)> wrote in message
> > news:7B735EE1-5157-4DB5-B98B-(E-Mail Removed)...
> > >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" <(E-Mail Removed)> 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.