Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I expand formula down a column when query results change? | Excel Discussion (Misc queries) | |||
I have a delay in formula results when I change one cell amount | Excel Discussion (Misc queries) | |||
Change results , change data in othe cells across the row | Excel Worksheet Functions | |||
How do I change formula results by changing the text color of the | Excel Discussion (Misc queries) | |||
Formula results in font color change | Excel Worksheet Functions |