| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct problem.
Hi,
I have the following formula: =SUMPRODUCT(--($A$10:$A$5000=Sheet2!$P$3),--($A$10:$A$5000<=Sheet2!$R$3),--($B$10:$B$5000=N3),--($E$10:$E$5000<TIME(41,40,0)),$D$10:$D$5000) What its suppose to do is to filters a criteria (col B) between two dates (col A), it then totals all the hours for that month under 41 hours and 40 minutes. I have a running total for that month on column E. Sheet2!$P$3 and Sheet2!$R$3 are the two dates. I get a figure but not the correct one. Can anybody help? -- capt |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumproduct problem.
capt,
This mod worked for me:- =SUMPRODUCT(--($A$10:$A$20=Sheet2!$P$3),--($A$10:$A$20<=Sheet2!$R$3),--($B$10:$B$20=N3),--($E$10:$E$20<TEXT(TIME(40,30,0),"hh:mm")),$D$10:$D $20) Note I shortened the ranges to test the formula by selecting eacch section in turn in the formula bar and tapping F9 which told me how the elements of the array were evaluating true,False etc. Select this bit and tap F9 $A$10:$A$20=Sheet2!$P$3 If the fix provided doesn't work then its probably your data and testing the formula should show the problem. Mike "capt" wrote: Hi, I have the following formula: =SUMPRODUCT(--($A$10:$A$5000=Sheet2!$P$3),--($A$10:$A$5000<=Sheet2!$R$3),--($B$10:$B$5000=N3),--($E$10:$E$5000<TIME(41,40,0)),$D$10:$D$5000) What its suppose to do is to filters a criteria (col B) between two dates (col A), it then totals all the hours for that month under 41 hours and 40 minutes. I have a running total for that month on column E. Sheet2!$P$3 and Sheet2!$R$3 are the two dates. I get a figure but not the correct one. Can anybody help? -- capt |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumproduct problem.
Thanks Mike,
It works well as follows: =SUMPRODUCT(--($A$10:$A$5000=Sheet2!$P$3),--($A$10:$A$5000<=Sheet2!$R$3),--($B$10:$B$5000=N3),--($E$10:$E$5000<TEXT(TIME(40,20,0),"hh:mm")),$D$10: $D$5000) But when I modify it for total hours above 41:40 it doesnt work. =SUMPRODUCT(--($A$10:$A$5000=Sheet2!$P$3),--($A$10:$A$5000<=Sheet2!$R$3),--($B$10:$B$5000=N2),--($E$10:$E$5000TEXT(TIME(41,40,0),"hh:mm")),$D$10: $D$5000) Is there something I doing wrong? -- capt "Mike H" wrote: capt, This mod worked for me:- =SUMPRODUCT(--($A$10:$A$20=Sheet2!$P$3),--($A$10:$A$20<=Sheet2!$R$3),--($B$10:$B$20=N3),--($E$10:$E$20<TEXT(TIME(40,30,0),"hh:mm")),$D$10:$D $20) Note I shortened the ranges to test the formula by selecting eacch section in turn in the formula bar and tapping F9 which told me how the elements of the array were evaluating true,False etc. Select this bit and tap F9 $A$10:$A$20=Sheet2!$P$3 If the fix provided doesn't work then its probably your data and testing the formula should show the problem. Mike "capt" wrote: Hi, I have the following formula: =SUMPRODUCT(--($A$10:$A$5000=Sheet2!$P$3),--($A$10:$A$5000<=Sheet2!$R$3),--($B$10:$B$5000=N3),--($E$10:$E$5000<TIME(41,40,0)),$D$10:$D$5000) What its suppose to do is to filters a criteria (col B) between two dates (col A), it then totals all the hours for that month under 41 hours and 40 minutes. I have a running total for that month on column E. Sheet2!$P$3 and Sheet2!$R$3 are the two dates. I get a figure but not the correct one. Can anybody help? -- capt |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumproduct problem.
Put your time in a cell formatted as [hh]:mm and then this formulsa
=SUMPRODUCT(--($A$10:$A$20=Sheet2!$P$3),--($A$10:$A$20<=Sheet2!$R$3),--($B$10:$B$20=N3),--($E$10:$E$20F10),$D$10:$D$20) Mike "capt" wrote: Thanks Mike, It works well as follows: =SUMPRODUCT(--($A$10:$A$5000=Sheet2!$P$3),--($A$10:$A$5000<=Sheet2!$R$3),--($B$10:$B$5000=N3),--($E$10:$E$5000<TEXT(TIME(40,20,0),"hh:mm")),$D$10: $D$5000) But when I modify it for total hours above 41:40 it doesnt work. =SUMPRODUCT(--($A$10:$A$5000=Sheet2!$P$3),--($A$10:$A$5000<=Sheet2!$R$3),--($B$10:$B$5000=N2),--($E$10:$E$5000TEXT(TIME(41,40,0),"hh:mm")),$D$10: $D$5000) Is there something I doing wrong? -- capt "Mike H" wrote: capt, This mod worked for me:- =SUMPRODUCT(--($A$10:$A$20=Sheet2!$P$3),--($A$10:$A$20<=Sheet2!$R$3),--($B$10:$B$20=N3),--($E$10:$E$20<TEXT(TIME(40,30,0),"hh:mm")),$D$10:$D $20) Note I shortened the ranges to test the formula by selecting eacch section in turn in the formula bar and tapping F9 which told me how the elements of the array were evaluating true,False etc. Select this bit and tap F9 $A$10:$A$20=Sheet2!$P$3 If the fix provided doesn't work then its probably your data and testing the formula should show the problem. Mike "capt" wrote: Hi, I have the following formula: =SUMPRODUCT(--($A$10:$A$5000=Sheet2!$P$3),--($A$10:$A$5000<=Sheet2!$R$3),--($B$10:$B$5000=N3),--($E$10:$E$5000<TIME(41,40,0)),$D$10:$D$5000) What its suppose to do is to filters a criteria (col B) between two dates (col A), it then totals all the hours for that month under 41 hours and 40 minutes. I have a running total for that month on column E. Sheet2!$P$3 and Sheet2!$R$3 are the two dates. I get a figure but not the correct one. Can anybody help? -- capt |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumproduct problem.
Did my earlier message not get through?
The number of hours in the TIME function can't be more than 23. Also, I don't see why you are using TEXT for the time condition if the column E figures are actual times, rather than text. -- David Biddulph "capt" wrote in message ... Thanks Mike, It works well as follows: =SUMPRODUCT(--($A$10:$A$5000=Sheet2!$P$3),--($A$10:$A$5000<=Sheet2!$R$3),--($B$10:$B$5000=N3),--($E$10:$E$5000<TEXT(TIME(40,20,0),"hh:mm")),$D$10: $D$5000) But when I modify it for total hours above 41:40 it doesnt work. =SUMPRODUCT(--($A$10:$A$5000=Sheet2!$P$3),--($A$10:$A$5000<=Sheet2!$R$3),--($B$10:$B$5000=N2),--($E$10:$E$5000TEXT(TIME(41,40,0),"hh:mm")),$D$10: $D$5000) Is there something I doing wrong? -- capt "Mike H" wrote: capt, This mod worked for me:- =SUMPRODUCT(--($A$10:$A$20=Sheet2!$P$3),--($A$10:$A$20<=Sheet2!$R$3),--($B$10:$B$20=N3),--($E$10:$E$20<TEXT(TIME(40,30,0),"hh:mm")),$D$10:$D $20) Note I shortened the ranges to test the formula by selecting eacch section in turn in the formula bar and tapping F9 which told me how the elements of the array were evaluating true,False etc. Select this bit and tap F9 $A$10:$A$20=Sheet2!$P$3 If the fix provided doesn't work then its probably your data and testing the formula should show the problem. Mike "capt" wrote: Hi, I have the following formula: =SUMPRODUCT(--($A$10:$A$5000=Sheet2!$P$3),--($A$10:$A$5000<=Sheet2!$R$3),--($B$10:$B$5000=N3),--($E$10:$E$5000<TIME(41,40,0)),$D$10:$D$5000) What its suppose to do is to filters a criteria (col B) between two dates (col A), it then totals all the hours for that month under 41 hours and 40 minutes. I have a running total for that month on column E. Sheet2!$P$3 and Sheet2!$R$3 are the two dates. I get a figure but not the correct one. Can anybody help? -- capt |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumproduct problem.
Check in Excel help for the TIME function, and you'll see its limitations.
-- David Biddulph "capt" wrote in message ... Hi, I have the following formula: =SUMPRODUCT(--($A$10:$A$5000=Sheet2!$P$3),--($A$10:$A$5000<=Sheet2!$R$3),--($B$10:$B$5000=N3),--($E$10:$E$5000<TIME(41,40,0)),$D$10:$D$5000) What its suppose to do is to filters a criteria (col B) between two dates (col A), it then totals all the hours for that month under 41 hours and 40 minutes. I have a running total for that month on column E. Sheet2!$P$3 and Sheet2!$R$3 are the two dates. I get a figure but not the correct one. Can anybody help? -- capt |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumproduct problem.
Thanks Mike,
I will try that, but at the moment it seems to have done the job. -- capt "David Biddulph" wrote: Check in Excel help for the TIME function, and you'll see its limitations. -- David Biddulph "capt" wrote in message ... Hi, I have the following formula: =SUMPRODUCT(--($A$10:$A$5000=Sheet2!$P$3),--($A$10:$A$5000<=Sheet2!$R$3),--($B$10:$B$5000=N3),--($E$10:$E$5000<TIME(41,40,0)),$D$10:$D$5000) What its suppose to do is to filters a criteria (col B) between two dates (col A), it then totals all the hours for that month under 41 hours and 40 minutes. I have a running total for that month on column E. Sheet2!$P$3 and Sheet2!$R$3 are the two dates. I get a figure but not the correct one. Can anybody help? -- capt |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumproduct problem.
The message you've replied to is mine, not Mike's.
If you are trying to use the TIME function with an hours figure greater than 23 (and you had 41 in your example), then you'll get the wrong result. -- David Biddulph "capt" wrote in message ... Thanks Mike, I will try that, but at the moment it seems to have done the job. -- capt "David Biddulph" wrote: Check in Excel help for the TIME function, and you'll see its limitations. -- David Biddulph "capt" wrote in message ... Hi, I have the following formula: =SUMPRODUCT(--($A$10:$A$5000=Sheet2!$P$3),--($A$10:$A$5000<=Sheet2!$R$3),--($B$10:$B$5000=N3),--($E$10:$E$5000<TIME(41,40,0)),$D$10:$D$5000) What its suppose to do is to filters a criteria (col B) between two dates (col A), it then totals all the hours for that month under 41 hours and 40 minutes. I have a running total for that month on column E. Sheet2!$P$3 and Sheet2!$R$3 are the two dates. I get a figure but not the correct one. Can anybody help? -- capt |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Problem with SUMPRODUCT....help please! | Excel Worksheet Functions | |||
| SUMPRODUCT problem | Excel Worksheet Functions | |||
| sumproduct problem? | Excel Discussion (Misc queries) | |||
| SUMPRODUCT problem | Excel Worksheet Functions | |||
| SUMPRODUCT Problem | Excel Discussion (Misc queries) | |||
Hybrid Mode
