Another SUMPRODUCT Query
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 |
Another SUMPRODUCT Query
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 |
Another SUMPRODUCT Query
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 |
Another SUMPRODUCT Query
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 |
Another SUMPRODUCT Query
"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. |
All times are GMT +1. The time now is 12:53 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com