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

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

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

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

=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
---

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

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

well, guess another discussion bites the dust here ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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

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

Thanks for posting back.
Go with what works best for you.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---