Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct with Condition OR Sumproduct with ADDRESS function - HE | Excel Discussion (Misc queries) | |||
lookup using column headers and row headers | Excel Discussion (Misc queries) | |||
Headers | Excel Discussion (Misc queries) | |||
Conditional SUMPRODUCT or SUMPRODUCT with Filters | Excel Worksheet Functions | |||
Help with Headers | Excel Discussion (Misc queries) |