finding and summing data in somewhat complex matrix.

[Willem]

Hi,

I am having trouble with the following, and wonder if someone can help me out.
We have a periodical dump of ledgerdata, which is divided in Budget and
Realisation info.
The format (i.e. the number of lines with Budget- or Realisation data)
varies per period.
Furthermore the data is specified (columnwise) over approx. 30 cust-units.
The data has to be reported per cost-unit on separate sheets, and is
ultimately aggregated to company-level.

The dataset looks like this:
Type Ledger# unit1 unit2 unit3 unitn
Budget 1 10 20 30 50
Budget 1 5 15 25 33
Budget 4 1 0 10 15
Real. 1 5 10 20 25
Real. 4 7 15 20 38
Real. 4 2 5 5 5

I am looking for a formula that finds whether it is a Budget or Realisation,
and then sums the values per ledger# per cost-unit.
Presently I find the data by defining the exact range (e.g. Budget data is
in A2:AZ136), and then use SUMIF to sum the data per ledger#.
With 30 cost-units this means a lot of find&replace.
Any suggestions?

Thanks!

 

[Mike H]

If ive understood correctly this could work:-

=SUMPRODUCT((A1:A10="Budget")*(B1:B10=1)*(C1:F10))


Alter the ranges to suit. This finds Budget in Col A, No1 in Col B and then
sums cols C,D,E & F

Mike

 

[Toppers]

If you want it by unit ....

=SUMPRODUCT(($A$2:$A$6="Budget")*($B$2:$B$6=1)*($C$1:$F$1="Unit3")*
($C$2:$F$6))

 

[Willem]

Mike, thanks for your quick reply, but no, it did not help me.
Maybe I was not quite clear in my question, but I'll try again.
I want to sum data per cost-unit, if 2 criteria are met.
The data is organised in ranges, identified by the text "Realisation" or
"Budget" in column A (criterium 1). The next criterium is in column D, and
consists of a number.
There are multiple rows containing data matching both criteria, and these
data i want to total per cost-unit.
I tried nesting IF-statements, combined IF with SUMIF, tried VLOOKUP, but
tio no avail.
If I confine the range tot the part with only Realisation, it seems to work
well. The problem occurs when I expand the range to the full dataset; then I
get a total of al elements in the column I am evaluating.
Hope that this is the info you (or anybody else) helps to come up with the
correct solution.
Keeping my fingers crossed.....

 

[Toppers]

Did you see my earlier reply?

If this does not meet your requirements, can you show us the format of your
output?

 

[Willem]

Yes I did see your earlier reply. Tried it, but no result. In your answer I
see a *; Sumproduct gives a comma to separate the ranges in my Excel-version
(2003).
I am not sure what you mean by "format of output"?
Below find a small part of my dataset. The fields "Type" and "Position" are
my criteria.
Wat I need is a solution that returns for position 1, type Realisation, the
total of 101.2+8.8+1.2+36 (for Unit 10).
I have approx. 135 rows of data of type Realisation, with about 25 different
positions. The same goes for type Budget, and the number of rows per type
vary.
There are 26 CostUnits (identified by a number)

Type Ledger Title Position
Unit10 Unit11
REALISATIE 40000 Salarissen contr 1 101.2 68.3
REALISATIE 40002 bruto inh fietsplan 1
REALISATIE 40020 Overwerk 1
REALISATIE 40035 Markttoeslag 1 0.3
REALISATIE 40040 Vakantietoeslag 1 8.8 6
REALISATIE 40045 Verlof 1 2.2 5.9
REALISATIE 40046 Ouderschapsverlof 1
REALISATIE 40050 Ploegentoeslag 1
REALISATIE 40051 Watertoeslag 1
REALISATIE 40052 Meetreistoeslag 1
REALISATIE 40053 Duiktoeslag 1
REALISATIE 40056 Bijzondere toelage 1
REALISATIE 40058 Uitzendtoelage 1
REALISATIE 40060 Gratificaties 1
REALISATIE 40070 ResAfhMaatreg Vast 5 1 3.2
REALISATIE 40071 ResAfhMaatreg Var A 1 36
REALISATIE 40072 ResAfhMaatreg Var B 1
REALISATIE 40076 ResAfhMaatreg Var A 5
REALISATIE 40077 ResAfhMaatreg Var B 5 3.5 17.9

Hope this helps........
Thanks for your efforts!!

Willem

 

[Toppers]

=SUMPRODUCT(($A$2:$A$17="REALISATIE")*($D$2:$D$17=1)*($E$1:$F$1="Unit10")*($E$2:$F$17))

If you replaced the * with ; you would get 0!

Copy as above and change the ranges.

This is the same as my original (except different columns)

 

[Willem]

Toppers,

You really are a Topper! It works like a charm.
Thanks for your help and patience!
I'm sure my manager will be pleased with this........

Willem.