Nesting sumif functions

[JT]

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

 

[Don Guillett]

try this. modify to suit
=sumproduct((a2:a22=250)*(b2:b22=25)*h2:h22)

 

[JT]

So here is the formula that I used:
=SUMPRODUCT((A2:A1480="250")*(F2:F1480=25)*H2:H1480)
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.

 

[Max]

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 B11
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,,MATCH(G2,$B$1:$D$1,0)))
and copy down. Adapt to suit.

 

[Max]

=SUMPRODUCT((A2:A1480="250")*(F2:F1480=25)*H2:H1480)

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:H1480)

---

 

[JT]

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]

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]

well, guess another discussion bites the dust here ..

---

 

[JT]

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?

 

[JT]

I ended up just using a pivot table which seems to work the best and is much
faster.

 

[Max]

Thanks for posting back.
Go with what works best for you.

---