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