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

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

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

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

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

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

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

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