|
Nesting sumif functions
I am trying to nest a sumif function but having trouble.
I have a spreadsheet that lists multiple cost/revenue types with multiple business units. I am trying to find a sum of a column for a given business unit and cost/revenue type. I.E., If the business unit is 250 & the cost/revenue type is 25 then sum column H. Thanks for the help. JT |
Nesting sumif functions
try this. modify to suit
=sumproduct((a2:a22=250)*(b2:b22=25)*h2:h22) -- Don Guillett Microsoft MVP Excel SalesAid Software "JT" wrote in message ... I am trying to nest a sumif function but having trouble. I have a spreadsheet that lists multiple cost/revenue types with multiple business units. I am trying to find a sum of a column for a given business unit and cost/revenue type. I.E., If the business unit is 250 & the cost/revenue type is 25 then sum column H. Thanks for the help. JT |
Nesting sumif functions
So here is the formula that I used:
=SUMPRODUCT((A2:A1480="250")*(F2:F1480=25)*H2:H148 0) the answer came out as $0......after doing a quick sort and total....the answer should have been $256,424.00 Any other ideas? Thanks. "Don Guillett" wrote: try this. modify to suit =sumproduct((a2:a22=250)*(b2:b22=25)*h2:h22) -- Don Guillett Microsoft MVP Excel SalesAid Software "JT" wrote in message ... I am trying to nest a sumif function but having trouble. I have a spreadsheet that lists multiple cost/revenue types with multiple business units. I am trying to find a sum of a column for a given business unit and cost/revenue type. I.E., If the business unit is 250 & the cost/revenue type is 25 then sum column H. Thanks for the help. JT |
Nesting sumif functions
One view, shown in this sample:
http://www.freefilehosting.net/download/3b8c5 Sumproduct_1.xls Assuming the BUs are listed in A2 down, cost/rev types listed in B1:D1 where BU#s may appear repeatedly in A2 down, but cost/rev types in B1 across are unique Assume pair inputs for BU and cost/rev made in F2:G2 down, eg: In F2: 250 In G2: 25 Then you could place in H2: =SUMPRODUCT(($A$2:$A$100=F2)*OFFSET($A$2:$A$100,,M ATCH(G2,$B$1:$D$1,0))) and copy down. Adapt to suit. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "JT" wrote: I am trying to nest a sumif function but having trouble. I have a spreadsheet that lists multiple cost/revenue types with multiple business units. I am trying to find a sum of a column for a given business unit and cost/revenue type. I.E., If the business unit is 250 & the cost/revenue type is 25 then sum column H. Thanks for the help. JT |
Nesting sumif functions
=SUMPRODUCT((A2:A1480="250")*(F2:F1480=25)*H2:H148 0)
It's probably a data consistency issue (maybe text numbers/mix data are throwing things off) Some guesses to get you going Try these: =SUMPRODUCT((A2:A1480=250)*(F2:F1480=25)*H2:H1480) =SUMPRODUCT((A2:A1480="250")*(F2:F1480="25")*H2:H1 480) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Nesting sumif functions
I have about 35 BU which are all listed in a column multiple times, as are
the cost/revenue types. I then want to add the total budget for a specific bu and c/r type. Here is a very simplified mockup of my spreadsheet. Ideally I would like to find a total budget for bu 250 and c/r 51, etc. BU C/R Type Budget Actual 250 51 10 5 035 51 20 25 150 51 10 10 035 52 15 15 250 52 15 10 250 53 30 35 250 51 10 5 035 52 20 20 150 51 10 10 "Max" wrote: One view, shown in this sample: http://www.freefilehosting.net/download/3b8c5 Sumproduct_1.xls Assuming the BUs are listed in A2 down, cost/rev types listed in B1:D1 where BU#s may appear repeatedly in A2 down, but cost/rev types in B1 across are unique Assume pair inputs for BU and cost/rev made in F2:G2 down, eg: In F2: 250 In G2: 25 Then you could place in H2: =SUMPRODUCT(($A$2:$A$100=F2)*OFFSET($A$2:$A$100,,M ATCH(G2,$B$1:$D$1,0))) and copy down. Adapt to suit. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "JT" wrote: I am trying to nest a sumif function but having trouble. I have a spreadsheet that lists multiple cost/revenue types with multiple business units. I am trying to find a sum of a column for a given business unit and cost/revenue type. I.E., If the business unit is 250 & the cost/revenue type is 25 then sum column H. Thanks for the help. JT |
Nesting sumif functions
Try it like this:
=SUMPRODUCT(($A$2:$A$1500=TEXT(F2,"000"))*($B$2:$B $1500=G2)*$C$2:$C$1500) See sample: http://www.freefilehosting.net/download/3b8dj Sumproduct_1a.xls -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "JT" wrote in message ... I have about 35 BU which are all listed in a column multiple times, as are the cost/revenue types. I then want to add the total budget for a specific bu and c/r type. Here is a very simplified mockup of my spreadsheet. Ideally I would like to find a total budget for bu 250 and c/r 51, etc. BU C/R Type Budget Actual 250 51 10 5 035 51 20 25 150 51 10 10 035 52 15 15 250 52 15 10 250 53 30 35 250 51 10 5 035 52 20 20 150 51 10 10 |
Nesting sumif functions
well, guess another discussion bites the dust here ..
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Nesting sumif functions
That still didn't work, thanks for trying though.
Could it be a problem because I am still using office 2003? Does anyone else have any additional ideas? "Max" wrote: Try it like this: =SUMPRODUCT(($A$2:$A$1500=TEXT(F2,"000"))*($B$2:$B $1500=G2)*$C$2:$C$1500) See sample: http://www.freefilehosting.net/download/3b8dj Sumproduct_1a.xls -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "JT" wrote in message ... I have about 35 BU which are all listed in a column multiple times, as are the cost/revenue types. I then want to add the total budget for a specific bu and c/r type. Here is a very simplified mockup of my spreadsheet. Ideally I would like to find a total budget for bu 250 and c/r 51, etc. BU C/R Type Budget Actual 250 51 10 5 035 51 20 25 150 51 10 10 035 52 15 15 250 52 15 10 250 53 30 35 250 51 10 5 035 52 20 20 150 51 10 10 |
Nesting sumif functions
I ended up just using a pivot table which seems to work the best and is much
faster. "JT" wrote: That still didn't work, thanks for trying though. Could it be a problem because I am still using office 2003? Does anyone else have any additional ideas? "Max" wrote: Try it like this: =SUMPRODUCT(($A$2:$A$1500=TEXT(F2,"000"))*($B$2:$B $1500=G2)*$C$2:$C$1500) See sample: http://www.freefilehosting.net/download/3b8dj Sumproduct_1a.xls -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "JT" wrote in message ... I have about 35 BU which are all listed in a column multiple times, as are the cost/revenue types. I then want to add the total budget for a specific bu and c/r type. Here is a very simplified mockup of my spreadsheet. Ideally I would like to find a total budget for bu 250 and c/r 51, etc. BU C/R Type Budget Actual 250 51 10 5 035 51 20 25 150 51 10 10 035 52 15 15 250 52 15 10 250 53 30 35 250 51 10 5 035 52 20 20 150 51 10 10 |
Nesting sumif functions
Thanks for posting back.
Go with what works best for you. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
| All times are GMT +1. The time now is 04:50 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com