Using Sumproduct with Headers
 

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?

Using Sumproduct with Headers
 

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.

--
If this helps, please remember to click yes.


"Brian" wrote:

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?

Using Sumproduct with Headers
 

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

"Paul C" wrote:

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.

--
If this helps, please remember to click yes.


"Brian" wrote:

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?

Using Sumproduct with Headers
 

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!

--------------------
(Ms-Exl-Learner)
--------------------



"Brian" wrote:

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?

Using Sumproduct with Headers
 

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

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

"Paul C" wrote:

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.

--
If this helps, please remember to click yes.


"Brian" wrote:

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?

--

Dave Peterson

Using Sumproduct with Headers
 

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?

"Paul C" wrote:

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.

--
If this helps, please remember to click yes.


"Brian" wrote:

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?