Abrupt change in formula results using sumproduct
 

I'm stumped. I have two worksheets where I count the occurrences of the work
order type by month from the first worksheet and get a running tally in a
table on the second worksheet. My results have been accurate until I arrived
at January. Column C is WS2 is accurate but columns D & E no longer tally
and count the blank cells which never happened in the past. Nothing has
changed except for the month value. Here is the set up:

Worksheet 1 is "Work Orders" Worksheet 2 is Totals FY 2009
Worksheet 1:
Column b is the Work Order Type
Phone Request
Telephone Trouble
Trouble Calls
Work Request
Column c is the date received
column d is the date started
column e is the date completed

Here is my formula:
=SUMPRODUCT(--('WORK ORDER'!B2:B701="Telephone Trouble"),--(--MONTH('WORK
ORDER'!D2:D701)=1))

Abrupt change in formula results using sumproduct
 

Your formula looks good. Could you post what data/dates you have entered, as
well as what your formula is currently returning(compared to what it should)?
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"mbeemom" wrote:

I'm stumped. I have two worksheets where I count the occurrences of the work
order type by month from the first worksheet and get a running tally in a
table on the second worksheet. My results have been accurate until I arrived
at January. Column C is WS2 is accurate but columns D & E no longer tally
and count the blank cells which never happened in the past. Nothing has
changed except for the month value. Here is the set up:

Worksheet 1 is "Work Orders" Worksheet 2 is Totals FY 2009
Worksheet 1:
Column b is the Work Order Type
Phone Request
Telephone Trouble
Trouble Calls
Work Request
Column c is the date received
column d is the date started
column e is the date completed

Here is my formula:
=SUMPRODUCT(--('WORK ORDER'!B2:B701="Telephone Trouble"),--(--MONTH('WORK
ORDER'!D2:D701)=1))

Abrupt change in formula results using sumproduct
 

WORK ORDER:
B C D E

Work Request 5-Jan-09 6-Jan-09 6-Jan-09
Work Request 5-Jan-09
Work Request 5-Jan-09
Phone Request 5-Jan-09 6-Jan-09 6-Jan-09
Phone Request 5-Jan-09
Trouble Call 6-Jan-09
Work Request 2-Jan-09 5-Jan-09 5-Jan-09
Work Request 6-Jan-09 6-Jan-09 6-Jan-09
Work Request 7-Jan-09
Work Request 8-Jan-09

Totals FY 2009:
A B C D
Jan-09 Received Started Finished
Phone Request 3 10 10
Telephone Trouble 0 0 0
Trouble Call 1 3 3
Work Request 8 10 11
TOTAL 12 23 24


"Luke M" wrote:

Your formula looks good. Could you post what data/dates you have entered, as
well as what your formula is currently returning(compared to what it should)?
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"mbeemom" wrote:

I'm stumped. I have two worksheets where I count the occurrences of the work
order type by month from the first worksheet and get a running tally in a
table on the second worksheet. My results have been accurate until I arrived
at January. Column C is WS2 is accurate but columns D & E no longer tally
and count the blank cells which never happened in the past. Nothing has
changed except for the month value. Here is the set up:

Worksheet 1 is "Work Orders" Worksheet 2 is Totals FY 2009
Worksheet 1:
Column b is the Work Order Type
Phone Request
Telephone Trouble
Trouble Calls
Work Request
Column c is the date received
column d is the date started
column e is the date completed

Here is my formula:
=SUMPRODUCT(--('WORK ORDER'!B2:B701="Telephone Trouble"),--(--MONTH('WORK
ORDER'!D2:D701)=1))

Abrupt change in formula results using sumproduct
 

Maybe you need to test the year as well

=SUMPRODUCT(--('WORK ORDER'!$B$2:$B$70=$A4),--(MONTH('WORK
ORDER'!$D$2:$D$70)=MONTH($A$1)),--(YEAR('WORK
ORDER'!$D$2:$D$70)=YEAR($A$1)))

--
__________________________________
HTH

Bob

"mbeemom" wrote in message
...
WORK ORDER:
B C D E

Work Request 5-Jan-09 6-Jan-09 6-Jan-09
Work Request 5-Jan-09
Work Request 5-Jan-09
Phone Request 5-Jan-09 6-Jan-09 6-Jan-09
Phone Request 5-Jan-09
Trouble Call 6-Jan-09
Work Request 2-Jan-09 5-Jan-09 5-Jan-09
Work Request 6-Jan-09 6-Jan-09 6-Jan-09
Work Request 7-Jan-09
Work Request 8-Jan-09

Totals FY 2009:
A B C D
Jan-09 Received Started Finished
Phone Request 3 10 10
Telephone Trouble 0 0 0
Trouble Call 1 3 3
Work Request 8 10 11
TOTAL 12 23 24


"Luke M" wrote:

Your formula looks good. Could you post what data/dates you have entered,
as
well as what your formula is currently returning(compared to what it
should)?
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"mbeemom" wrote:

I'm stumped. I have two worksheets where I count the occurrences of
the work
order type by month from the first worksheet and get a running tally in
a
table on the second worksheet. My results have been accurate until I
arrived
at January. Column C is WS2 is accurate but columns D & E no longer
tally
and count the blank cells which never happened in the past. Nothing
has
changed except for the month value. Here is the set up:

Worksheet 1 is "Work Orders" Worksheet 2 is Totals FY 2009
Worksheet 1:
Column b is the Work Order Type
Phone Request
Telephone Trouble
Trouble Calls
Work Request
Column c is the date received
column d is the date started
column e is the date completed

Here is my formula:
=SUMPRODUCT(--('WORK ORDER'!B2:B701="Telephone
Trouble"),--(--MONTH('WORK
ORDER'!D2:D701)=1))

Abrupt change in formula results using sumproduct
 

I tried your suggestion and my answer was zero. I'm not sure I understand
how the formula stopped working as designed from last month to this month. I
had the same spreadsheet for last fiscal year and the results were accurate
month to month. Is there another step I can try?

"Bob Phillips" wrote:

Maybe you need to test the year as well

=SUMPRODUCT(--('WORK ORDER'!$B$2:$B$70=$A4),--(MONTH('WORK
ORDER'!$D$2:$D$70)=MONTH($A$1)),--(YEAR('WORK
ORDER'!$D$2:$D$70)=YEAR($A$1)))

--
__________________________________
HTH

Bob

"mbeemom" wrote in message
...
WORK ORDER:
B C D E

Work Request 5-Jan-09 6-Jan-09 6-Jan-09
Work Request 5-Jan-09
Work Request 5-Jan-09
Phone Request 5-Jan-09 6-Jan-09 6-Jan-09
Phone Request 5-Jan-09
Trouble Call 6-Jan-09
Work Request 2-Jan-09 5-Jan-09 5-Jan-09
Work Request 6-Jan-09 6-Jan-09 6-Jan-09
Work Request 7-Jan-09
Work Request 8-Jan-09

Totals FY 2009:
A B C D
Jan-09 Received Started Finished
Phone Request 3 10 10
Telephone Trouble 0 0 0
Trouble Call 1 3 3
Work Request 8 10 11
TOTAL 12 23 24


"Luke M" wrote:

Your formula looks good. Could you post what data/dates you have entered,
as
well as what your formula is currently returning(compared to what it
should)?
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"mbeemom" wrote:

I'm stumped. I have two worksheets where I count the occurrences of
the work
order type by month from the first worksheet and get a running tally in
a
table on the second worksheet. My results have been accurate until I
arrived
at January. Column C is WS2 is accurate but columns D & E no longer
tally
and count the blank cells which never happened in the past. Nothing
has
changed except for the month value. Here is the set up:

Worksheet 1 is "Work Orders" Worksheet 2 is Totals FY 2009
Worksheet 1:
Column b is the Work Order Type
Phone Request
Telephone Trouble
Trouble Calls
Work Request
Column c is the date received
column d is the date started
column e is the date completed

Here is my formula:
=SUMPRODUCT(--('WORK ORDER'!B2:B701="Telephone
Trouble"),--(--MONTH('WORK
ORDER'!D2:D701)=1))