Using Sumproduct with Headers

[Brian]

I have data that is being exported into Excel from a bank database.
I'd like to use a sumproduct formula to establish totals, however the
exported data is coming across with some headers in the rows.

EG.

Bank Name Investment Type Amount
Bank A Money Market $100
Bank B CD $500
Bank C Money Market $500
Bank B Stock $25

Bank Name Investment Type Amount
Bank A Stock $100
Bank C Stock $400
Bank B CD $50

Ideally I'd like to come up with a formula on a seperate tab that will give
me the total balance of Bank B CD's...(in the case above $550)...but since
there are headers in between, I'm getting a #VALUE result.
Anyway to come up with this answer by not deleting the header rows or not
using filter?

 

[Paul C]

A properly constructed sumproduct shold work

I loaded data and successfully used this formula to get the Bank B CD
subtotal.

=SUMPRODUCT(--(Sheet1!A1:A10="Bank B"),--(Sheet1!C1:C10="CD"),Sheet1!E1:E10)

Double check your formula. There could be a typo. The other issue may be
the formatting of the data or some error buried in it. If one of the arrays
(especially the amout ) has an error the formula will error.

Note: When constructing a conditional sum product the are a few ways to do
it. I prefer --(A1:A10="Bank B") because the -- forces an evaluation and
makes formula tracing easier. This is something I found discussed in this
group before and ever since I started using the -- method, I seem to have
less issues construction conditional sumproducts.

 

[Brian]

Thanks Paul,
That did the trick....
I was using the Sumproduct (()*()*()) method....and didn't work for some
reason...but your method worked.

 

[Ms-Exl-Learner]

Try this...

Paste the below mentioned formula in your desired cell.

=SUM((A2:A10="BANK B")*(B2:B10="CD")*(C2:C10))

after pasting the formula press F2 and press CNTRL+SHIFT+ENTER, since it is
an array formula. Once you have pressed CNTRL+SHIFT+ENTER your formula will
look like this

{=SUM((A2:A10="BANK B")*(B2:B10="CD")*(C2:C10))}

If you want to use the formula in some other sheet then use this.

=SUM((Sheet1!A2:A10="BANK B")*(Sheet1!B2:B10="CD")*(Sheet1!C2:C10))

Dont forget the hit CNTRL+SHIFT+ENTER after pasting the formula.

If this post helps, Click Yes!

 

[Dave Peterson]

=1*0*"Someheaderstring"
will give you a #value! error--you can't multiply by those strings.

It's usually a good idea to share the offending formula in the original post.

 

[Brian]

Paul,
One more question I'm hoping you can help me with....
I actually have a 4th column that has interest rates in it.
I'd like to also calculate a weighted average for these rates....but can't
get it to work correctly.
Any ideas?