Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT with 3 criteria
I am not sure why the result always showing zero.
Column A contains name: A, B, C, A, C, ..etc Column B contains range of date: MM/DD/YYY Column C contains # of hours, some number are negatives I am trying to find out the # of hours for name A in a range of date, so I put the following equestion together, but it always return zero. =SUMPRODUCT(--($A$5:$A$417="A"),--($B$5:$B$417=39896),--($B$5:$B$417<=39909),($C$5:$C$417)) I am using MS 2003 thanks, Kit |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT with 3 criteria
It's working fine for me...are you sure you have serial dates entered into
column B, and not text? (try formatting the cells as number/general as a check) Or is it a weird fluke that the sum is actually zero? -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Kit" wrote: I am not sure why the result always showing zero. Column A contains name: A, B, C, A, C, ..etc Column B contains range of date: MM/DD/YYY Column C contains # of hours, some number are negatives I am trying to find out the # of hours for name A in a range of date, so I put the following equestion together, but it always return zero. =SUMPRODUCT(--($A$5:$A$417="A"),--($B$5:$B$417=39896),--($B$5:$B$417<=39909),($C$5:$C$417)) I am using MS 2003 thanks, Kit |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT with 3 criteria
The formula looks fine to me. A couple things you might check. Are you sure
you're using the correct serial numbers for the dates you want to check in column B? 39896 = March 24, 2009 and 39909 = April 6, 2009. Next, are your numbers in column C entered as Text? Adding a -- to your final criteria will take care of that. --($C$5:$C$417) HTH Elkar "Kit" wrote: I am not sure why the result always showing zero. Column A contains name: A, B, C, A, C, ..etc Column B contains range of date: MM/DD/YYY Column C contains # of hours, some number are negatives I am trying to find out the # of hours for name A in a range of date, so I put the following equestion together, but it always return zero. =SUMPRODUCT(--($A$5:$A$417="A"),--($B$5:$B$417=39896),--($B$5:$B$417<=39909),($C$5:$C$417)) I am using MS 2003 thanks, Kit |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT with 3 criteria
I did look into the format of the column of the data. It seems like the
problem is there and no matter what I do, I can't seems to change the format. I copied the data from a generated report and pasted to my excel workbook. When I click on the format for the column with the dates. All format shows example: 03/11/2009, even for General. The only way I can unlock the format of the cell is to retype the date manually on the cell and the format will correct itself. Is there anyway I can fix that problem? Thanks, Kit "Elkar" wrote: The formula looks fine to me. A couple things you might check. Are you sure you're using the correct serial numbers for the dates you want to check in column B? 39896 = March 24, 2009 and 39909 = April 6, 2009. Next, are your numbers in column C entered as Text? Adding a -- to your final criteria will take care of that. --($C$5:$C$417) HTH Elkar "Kit" wrote: I am not sure why the result always showing zero. Column A contains name: A, B, C, A, C, ..etc Column B contains range of date: MM/DD/YYY Column C contains # of hours, some number are negatives I am trying to find out the # of hours for name A in a range of date, so I put the following equestion together, but it always return zero. =SUMPRODUCT(--($A$5:$A$417="A"),--($B$5:$B$417=39896),--($B$5:$B$417<=39909),($C$5:$C$417)) I am using MS 2003 thanks, Kit |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT with 3 criteria
Often with imported data, the format will not be what you expect. Simply
changing the cell format will only apply to new data, not existing data. Try applying "Text to Columns" to your column of data. Leave all the options as default and click "Finish". This usually clears up any formatting problems. HTH Elkar "Kit" wrote: I did look into the format of the column of the data. It seems like the problem is there and no matter what I do, I can't seems to change the format. I copied the data from a generated report and pasted to my excel workbook. When I click on the format for the column with the dates. All format shows example: 03/11/2009, even for General. The only way I can unlock the format of the cell is to retype the date manually on the cell and the format will correct itself. Is there anyway I can fix that problem? Thanks, Kit "Elkar" wrote: The formula looks fine to me. A couple things you might check. Are you sure you're using the correct serial numbers for the dates you want to check in column B? 39896 = March 24, 2009 and 39909 = April 6, 2009. Next, are your numbers in column C entered as Text? Adding a -- to your final criteria will take care of that. --($C$5:$C$417) HTH Elkar "Kit" wrote: I am not sure why the result always showing zero. Column A contains name: A, B, C, A, C, ..etc Column B contains range of date: MM/DD/YYY Column C contains # of hours, some number are negatives I am trying to find out the # of hours for name A in a range of date, so I put the following equestion together, but it always return zero. =SUMPRODUCT(--($A$5:$A$417="A"),--($B$5:$B$417=39896),--($B$5:$B$417<=39909),($C$5:$C$417)) I am using MS 2003 thanks, Kit |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT with 3 criteria
You could use the DATEVALUE function then, like so:
=SUMPRODUCT(--($A$5:$A$417="A"),--(DATEVALUE($B$5:$B$417)=39896),--(DATEVALUE($B$5:$B$417)<=39909),($C$5:$C$417)) DATEVALUE converts text that "looks" like a date into an actual serial date. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Kit" wrote: I did look into the format of the column of the data. It seems like the problem is there and no matter what I do, I can't seems to change the format. I copied the data from a generated report and pasted to my excel workbook. When I click on the format for the column with the dates. All format shows example: 03/11/2009, even for General. The only way I can unlock the format of the cell is to retype the date manually on the cell and the format will correct itself. Is there anyway I can fix that problem? Thanks, Kit "Elkar" wrote: The formula looks fine to me. A couple things you might check. Are you sure you're using the correct serial numbers for the dates you want to check in column B? 39896 = March 24, 2009 and 39909 = April 6, 2009. Next, are your numbers in column C entered as Text? Adding a -- to your final criteria will take care of that. --($C$5:$C$417) HTH Elkar "Kit" wrote: I am not sure why the result always showing zero. Column A contains name: A, B, C, A, C, ..etc Column B contains range of date: MM/DD/YYY Column C contains # of hours, some number are negatives I am trying to find out the # of hours for name A in a range of date, so I put the following equestion together, but it always return zero. =SUMPRODUCT(--($A$5:$A$417="A"),--($B$5:$B$417=39896),--($B$5:$B$417<=39909),($C$5:$C$417)) I am using MS 2003 thanks, Kit |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT with 3 criteria
Thank Elkar,
Your method works :) Kit Your "Elkar" wrote: Often with imported data, the format will not be what you expect. Simply changing the cell format will only apply to new data, not existing data. Try applying "Text to Columns" to your column of data. Leave all the options as default and click "Finish". This usually clears up any formatting problems. HTH Elkar "Kit" wrote: I did look into the format of the column of the data. It seems like the problem is there and no matter what I do, I can't seems to change the format. I copied the data from a generated report and pasted to my excel workbook. When I click on the format for the column with the dates. All format shows example: 03/11/2009, even for General. The only way I can unlock the format of the cell is to retype the date manually on the cell and the format will correct itself. Is there anyway I can fix that problem? Thanks, Kit "Elkar" wrote: The formula looks fine to me. A couple things you might check. Are you sure you're using the correct serial numbers for the dates you want to check in column B? 39896 = March 24, 2009 and 39909 = April 6, 2009. Next, are your numbers in column C entered as Text? Adding a -- to your final criteria will take care of that. --($C$5:$C$417) HTH Elkar "Kit" wrote: I am not sure why the result always showing zero. Column A contains name: A, B, C, A, C, ..etc Column B contains range of date: MM/DD/YYY Column C contains # of hours, some number are negatives I am trying to find out the # of hours for name A in a range of date, so I put the following equestion together, but it always return zero. =SUMPRODUCT(--($A$5:$A$417="A"),--($B$5:$B$417=39896),--($B$5:$B$417<=39909),($C$5:$C$417)) I am using MS 2003 thanks, Kit |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT with 3 criteria
Thanks for your help Luke.
Kit "Luke M" wrote: You could use the DATEVALUE function then, like so: =SUMPRODUCT(--($A$5:$A$417="A"),--(DATEVALUE($B$5:$B$417)=39896),--(DATEVALUE($B$5:$B$417)<=39909),($C$5:$C$417)) DATEVALUE converts text that "looks" like a date into an actual serial date. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Kit" wrote: I did look into the format of the column of the data. It seems like the problem is there and no matter what I do, I can't seems to change the format. I copied the data from a generated report and pasted to my excel workbook. When I click on the format for the column with the dates. All format shows example: 03/11/2009, even for General. The only way I can unlock the format of the cell is to retype the date manually on the cell and the format will correct itself. Is there anyway I can fix that problem? Thanks, Kit "Elkar" wrote: The formula looks fine to me. A couple things you might check. Are you sure you're using the correct serial numbers for the dates you want to check in column B? 39896 = March 24, 2009 and 39909 = April 6, 2009. Next, are your numbers in column C entered as Text? Adding a -- to your final criteria will take care of that. --($C$5:$C$417) HTH Elkar "Kit" wrote: I am not sure why the result always showing zero. Column A contains name: A, B, C, A, C, ..etc Column B contains range of date: MM/DD/YYY Column C contains # of hours, some number are negatives I am trying to find out the # of hours for name A in a range of date, so I put the following equestion together, but it always return zero. =SUMPRODUCT(--($A$5:$A$417="A"),--($B$5:$B$417=39896),--($B$5:$B$417<=39909),($C$5:$C$417)) I am using MS 2003 thanks, Kit |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
add two criteria that should not be met to sumproduct | Excel Worksheet Functions | |||
Sumproduct - two+ criteria | Excel Worksheet Functions | |||
Sumproduct 4 criteria | Excel Worksheet Functions | |||
SUMPRODUCT with 3 criteria | Excel Discussion (Misc queries) | |||
Sumproduct with 2 criteria | Excel Worksheet Functions |