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

 

[David Biddulph]

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.

 

[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.