Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
finding and summing data in somewhat complex matrix.
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
finding and summing data in somewhat complex matrix.
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 "Willem" wrote: 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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
finding and summing data in somewhat complex matrix.
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" wrote: 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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
finding and summing data in somewhat complex matrix.
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..... "Mike H" wrote: 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 "Willem" wrote: 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! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
finding and summing data in somewhat complex matrix.
Did you see my earlier reply?
If this does not meet your requirements, can you show us the format of your output? "Willem" wrote: 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..... "Mike H" wrote: 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 "Willem" wrote: 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! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
finding and summing data in somewhat complex matrix.
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" wrote: Did you see my earlier reply? If this does not meet your requirements, can you show us the format of your output? "Willem" wrote: 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..... "Mike H" wrote: 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 "Willem" wrote: 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! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
finding and summing data in somewhat complex matrix.
=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" wrote: 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" wrote: Did you see my earlier reply? If this does not meet your requirements, can you show us the format of your output? "Willem" wrote: 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..... "Mike H" wrote: 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 "Willem" wrote: 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! |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
finding and summing data in somewhat complex matrix.
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. "Toppers" wrote: =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" wrote: 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" wrote: Did you see my earlier reply? If this does not meet your requirements, can you show us the format of your output? "Willem" wrote: 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..... "Mike H" wrote: 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 "Willem" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
complex criteria summing | Excel Discussion (Misc queries) | |||
Where to find complex matrix math add-ins for Excel 2003? | Excel Worksheet Functions | |||
How to compute the inverse of a matrix with some complex elements | Excel Worksheet Functions | |||
How to do matrix operation with complex number elements in Excel | Excel Worksheet Functions | |||
Matrix operations with complex numbers | Excel Discussion (Misc queries) |