Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct with nested if statement
Hi
I have been attempting to resolve this issue for a while, having looked both here and at MediaKent.co.uk My issue is that i have a worksheet containing financial data in the following format Column A = Nominal ledger Code (format nnnn) Column B = Department Code (format n) Column C = Transaction Date (format nnnnn) Column D = Transaction Value (format NNNNN.NN) I have tried several SumProduct statements, and have overcome the date issue by creating a seperate spreadsheet with the months converted to a general format as the sumproduct didnt seem to like the column being set with a mmyy format. I need to pull values for particular nominal ledger codes, department numbers, falling within a date range into a seperate sheet. I am using the following formula =SUMPRODUCT((Sheet1!D1:D16201)*(Sheet1!A1:A16201=" 4000")*(Sheet1!B1:B16201=1)*(Sheet1!E1:E16201=She et2!B1)*(Sheet1!E1:E16201<=Sheet2!B2))*-1 Where 4000 = nominal code, 1 = department number, and sheet 2 b1 and b2 hold the upper and lower numerical values for the date range. Once i have fathomed this bit out i will need to add additional criteria as some of the cells will hold the values of multiple nominal codes, and to ensure that future data that is pulled via ODBC from a sage accounting system i will need to include currently empty cells in the calculations. Any help greatly appreciated and will also prevent me from going premeturely bald!!! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct with nested if statement
On Tue, 30 Dec 2008 07:01:01 -0800, Confused of Chingford! <Confused
of wrote: Hi I have been attempting to resolve this issue for a while, having looked both here and at MediaKent.co.uk My issue is that i have a worksheet containing financial data in the following format Column A = Nominal ledger Code (format nnnn) Column B = Department Code (format n) Column C = Transaction Date (format nnnnn) Column D = Transaction Value (format NNNNN.NN) I have tried several SumProduct statements, and have overcome the date issue by creating a seperate spreadsheet with the months converted to a general format as the sumproduct didnt seem to like the column being set with a mmyy format. I need to pull values for particular nominal ledger codes, department numbers, falling within a date range into a seperate sheet. I am using the following formula =SUMPRODUCT((Sheet1!D1:D16201)*(Sheet1!A1:A16201= "4000")*(Sheet1!B1:B16201=1)*(Sheet1!E1:E16201=Sh eet2!B1)*(Sheet1!E1:E16201<=Sheet2!B2))*-1 Where 4000 = nominal code, 1 = department number, and sheet 2 b1 and b2 hold the upper and lower numerical values for the date range. Once i have fathomed this bit out i will need to add additional criteria as some of the cells will hold the values of multiple nominal codes, and to ensure that future data that is pulled via ODBC from a sage accounting system i will need to include currently empty cells in the calculations. Any help greatly appreciated and will also prevent me from going premeturely bald!!! You forgot to mention what the problem is with the formula you use. Does it return the wrong value or does it return an error? What value do you expect an what do you get. Why do you use " with the Nominal ledger code ("4000") but not with the Department code (1)? Lars-Åke |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct with nested if statement
Hi Lars
The formula returns an incorrect value, if i filter the original spreadsheet and total the value of the columns meeting the criteria i get a different value to the value returned by my sumproduct statement! I have used "4000" and "1" but it made no differnce to the result. Phil "Lars-Ã…ke Aspelin" wrote: On Tue, 30 Dec 2008 07:01:01 -0800, Confused of Chingford! <Confused of wrote: Hi I have been attempting to resolve this issue for a while, having looked both here and at MediaKent.co.uk My issue is that i have a worksheet containing financial data in the following format Column A = Nominal ledger Code (format nnnn) Column B = Department Code (format n) Column C = Transaction Date (format nnnnn) Column D = Transaction Value (format NNNNN.NN) I have tried several SumProduct statements, and have overcome the date issue by creating a seperate spreadsheet with the months converted to a general format as the sumproduct didnt seem to like the column being set with a mmyy format. I need to pull values for particular nominal ledger codes, department numbers, falling within a date range into a seperate sheet. I am using the following formula =SUMPRODUCT((Sheet1!D1:D16201)*(Sheet1!A1:A16201= "4000")*(Sheet1!B1:B16201=1)*(Sheet1!E1:E16201=Sh eet2!B1)*(Sheet1!E1:E16201<=Sheet2!B2))*-1 Where 4000 = nominal code, 1 = department number, and sheet 2 b1 and b2 hold the upper and lower numerical values for the date range. Once i have fathomed this bit out i will need to add additional criteria as some of the cells will hold the values of multiple nominal codes, and to ensure that future data that is pulled via ODBC from a sage accounting system i will need to include currently empty cells in the calculations. Any help greatly appreciated and will also prevent me from going premeturely bald!!! You forgot to mention what the problem is with the formula you use. Does it return the wrong value or does it return an error? What value do you expect an what do you get. Why do you use " with the Nominal ledger code ("4000") but not with the Department code (1)? Lars-Ã…ke |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct with nested if statement
Firstly, throw away the quote marks, unless the 4000 and 1 are text strings.
If they are numbers, you don't want quote marks. If your data is pulled in from another system and contains text strings rather than numbers, you'll need to be careful and may need to convert the data. If in doubt use =ISNUMBER(cellref) and =ISTEXT(cellref) to check what type of data you've got. Secondly it is probable easier to use =-SUMPRODUCT(...) instead of =SUMPRODUCT(...)*-1 Thirdly, where you say "... b1 and b2 hold the upper and lower numerical values for the date range", I hope that you mean "... lower and upper ... " respectively. Fourthly, are you confused between columns C and E? Fifthly it sounds as if you may have been confused when you said "... have overcome the date issue by creating a seperate spreadsheet with the months converted to a general format as the sumproduct didnt seem to like the column being set with a mmyy format." The format in which you *display* a number doesn't affect the calculation. What matters is the underlying content of the cell. A date is the same date whether it is formatted as mmyy or ddd dd mm yyyy or even as General. And remember that a date formatted as mmyy to show 1208 is *not* the same as a simple number 1208. -- David Biddulph "Confused of Chingford!" wrote in message ... Hi Lars The formula returns an incorrect value, if i filter the original spreadsheet and total the value of the columns meeting the criteria i get a different value to the value returned by my sumproduct statement! I have used "4000" and "1" but it made no differnce to the result. Phil "Lars-Åke Aspelin" wrote: On Tue, 30 Dec 2008 07:01:01 -0800, Confused of Chingford! <Confused of wrote: Hi I have been attempting to resolve this issue for a while, having looked both here and at MediaKent.co.uk My issue is that i have a worksheet containing financial data in the following format Column A = Nominal ledger Code (format nnnn) Column B = Department Code (format n) Column C = Transaction Date (format nnnnn) Column D = Transaction Value (format NNNNN.NN) I have tried several SumProduct statements, and have overcome the date issue by creating a seperate spreadsheet with the months converted to a general format as the sumproduct didnt seem to like the column being set with a mmyy format. I need to pull values for particular nominal ledger codes, department numbers, falling within a date range into a seperate sheet. I am using the following formula =SUMPRODUCT((Sheet1!D1:D16201)*(Sheet1!A1:A16201= "4000")*(Sheet1!B1:B16201=1)*(Sheet1!E1:E16201=Sh eet2!B1)*(Sheet1!E1:E16201<=Sheet2!B2))*-1 Where 4000 = nominal code, 1 = department number, and sheet 2 b1 and b2 hold the upper and lower numerical values for the date range. Once i have fathomed this bit out i will need to add additional criteria as some of the cells will hold the values of multiple nominal codes, and to ensure that future data that is pulled via ODBC from a sage accounting system i will need to include currently empty cells in the calculations. Any help greatly appreciated and will also prevent me from going premeturely bald!!! You forgot to mention what the problem is with the formula you use. Does it return the wrong value or does it return an error? What value do you expect an what do you get. Why do you use " with the Nominal ledger code ("4000") but not with the Department code (1)? Lars-Åke |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct with nested if statement
Excel 2007
Pivot Table Filter by month, quarter, date range, YTD, group by ledger(s), department(s), values, ignore empty cells, all without a single formula: http://www.mediafire.com/file/hwbygedtmvz/12_30_08.xlsx |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct with nested if statement
Hi David
Thanks for the reply Quotes around numeric fileds now gone Using -sumproduct all fields are numeric In the actual spreadsheet column C is used to display the name of the departemnt only, column e is the date field The actual data is as shown below Sheet 1 Nominal Department Department Name Value Date 0010 0 165000 39599 0020 0 964511.4 39599 0020 0 -459.57 39727 0021 0 -5591.13 39629 0021 0 -5587.17 39660 0021 0 -517528.38 39599 0021 0 -5587.17 39691 0021 0 -5587.17 39721 0030 0 610.16 39767 0030 0 85 39672 0030 0 727.07 39670 0030 0 626.4 39675 0030 0 265 39629 0030 51 1800 39691 0030 0 445.5 39652 0030 0 610.16 39767 0030 0 681.01 39670 0030 0 700 39715 0030 0 178.03 39699 0030 0 610.16 39758 0030 0 405 39752 0040 0 49283.42 39599 0041 0 -14584.42 39599 0041 0 -434.91 39629 0041 0 -433.19 39660 0041 0 -433.19 39691 0041 0 -433.19 39721 0050 0 1450 39650 0050 0 -2553.19 39736 0050 0 205754.64 39599 0051 0 -2598.33 39660 0051 0 -2599.37 39629 0051 0 -81029.64 39599 0051 0 -2598.33 39721 0051 0 -2598.33 39691 0060 0 2 39599 0061 0 -2 39599 0070 0 39986.49 39599 1001 0 27375 39599 1100 1 PILING 12631.25 38748 1100 1 PILING 4314.6 39689 1100 0 -171380.03 39599 1100 1 PILING 5781 38854 1100 1 PILING 286 38748 1100 1 PILING 13250 38680 1100 1 PILING 4465 38856 1100 0 -197.28 39693 1100 1 PILING 420.65 39689 1100 1 PILING 6694.56 38856 Sheet 2 Jun-2008 39600 Jul-2008 39630 Aug-2008 39661 Sep-2008 39692 Oct-2008 39722 Nov-2008 39753 Dec-2008 39783 Jan-2009 39814 Feb-2009 39845 Mar-2009 39873 Apr-2009 39904 May-2009 39934 Jun-2009 39965 But i still get a different value to what i expect, even tracing the formula through! "David Biddulph" wrote: Firstly, throw away the quote marks, unless the 4000 and 1 are text strings. If they are numbers, you don't want quote marks. If your data is pulled in from another system and contains text strings rather than numbers, you'll need to be careful and may need to convert the data. If in doubt use =ISNUMBER(cellref) and =ISTEXT(cellref) to check what type of data you've got. Secondly it is probable easier to use =-SUMPRODUCT(...) instead of =SUMPRODUCT(...)*-1 Thirdly, where you say "... b1 and b2 hold the upper and lower numerical values for the date range", I hope that you mean "... lower and upper ... " respectively. Fourthly, are you confused between columns C and E? Fifthly it sounds as if you may have been confused when you said "... have overcome the date issue by creating a seperate spreadsheet with the months converted to a general format as the sumproduct didnt seem to like the column being set with a mmyy format." The format in which you *display* a number doesn't affect the calculation. What matters is the underlying content of the cell. A date is the same date whether it is formatted as mmyy or ddd dd mm yyyy or even as General. And remember that a date formatted as mmyy to show 1208 is *not* the same as a simple number 1208. -- David Biddulph "Confused of Chingford!" wrote in message ... Hi Lars The formula returns an incorrect value, if i filter the original spreadsheet and total the value of the columns meeting the criteria i get a different value to the value returned by my sumproduct statement! I have used "4000" and "1" but it made no differnce to the result. Phil "Lars-Ã…ke Aspelin" wrote: On Tue, 30 Dec 2008 07:01:01 -0800, Confused of Chingford! <Confused of wrote: Hi I have been attempting to resolve this issue for a while, having looked both here and at MediaKent.co.uk My issue is that i have a worksheet containing financial data in the following format Column A = Nominal ledger Code (format nnnn) Column B = Department Code (format n) Column C = Transaction Date (format nnnnn) Column D = Transaction Value (format NNNNN.NN) I have tried several SumProduct statements, and have overcome the date issue by creating a seperate spreadsheet with the months converted to a general format as the sumproduct didnt seem to like the column being set with a mmyy format. I need to pull values for particular nominal ledger codes, department numbers, falling within a date range into a seperate sheet. I am using the following formula =SUMPRODUCT((Sheet1!D1:D16201)*(Sheet1!A1:A16201= "4000")*(Sheet1!B1:B16201=1)*(Sheet1!E1:E16201=Sh eet2!B1)*(Sheet1!E1:E16201<=Sheet2!B2))*-1 Where 4000 = nominal code, 1 = department number, and sheet 2 b1 and b2 hold the upper and lower numerical values for the date range. Once i have fathomed this bit out i will need to add additional criteria as some of the cells will hold the values of multiple nominal codes, and to ensure that future data that is pulled via ODBC from a sage accounting system i will need to include currently empty cells in the calculations. Any help greatly appreciated and will also prevent me from going premeturely bald!!! You forgot to mention what the problem is with the formula you use. Does it return the wrong value or does it return an error? What value do you expect an what do you get. Why do you use " with the Nominal ledger code ("4000") but not with the Department code (1)? Lars-Ã…ke |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct with nested if statement
Thanks Herbert, but as i am to populate an already existing report with the
data a pivot table, although better solution, is not appropriate here. "Herbert Seidenberg" wrote: Excel 2007 Pivot Table Filter by month, quarter, date range, YTD, group by ledger(s), department(s), values, ignore empty cells, all without a single formula: http://www.mediafire.com/file/hwbygedtmvz/12_30_08.xlsx |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nested IF Statement Help | Excel Discussion (Misc queries) | |||
IF statement with nested SUMPRODUCT? | Excel Discussion (Misc queries) | |||
IF OR NESTED STATEMENT | Excel Discussion (Misc queries) | |||
Nested IF/AND Statement | Excel Discussion (Misc queries) | |||
Nested if then else statement | Excel Worksheet Functions |