Sumproduct with nested if statement

[Confused of Chingford!]

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!D116201)*(Sheet1!A1:A16201="4000")*(Sheet1!B1:B16201=1)*(Sheet1!E1:E16201>=Sheet2!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!!!

 

[Lars-Åke Aspelin]

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!D116201)*(Sheet1!A1:A16201="4000")*(Sheet1!B1:B16201=1)*(Sheet1!E1:E16201>=Sheet2!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

 

[Confused of Chingford!]

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

 

[Herbert Seidenberg]

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

 

[Confused of Chingford!]

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!

 

[Confused of Chingford!]

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.