| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT Query
Why is it if i build a SUMPRODUCT formula around existing data in one of my sheets it works fine, but it won't 'pick up' data from a blank sheet which then has data dumped into it? i.e. Sheet 1 has 2 columns of data, product sold and by who. In Sheet 2 i have 10 columns (B to K) listing the different products available to sell. Column A has my salesperson's initials (all unique). Because i have to keep each days data on a seperate workbook i'd like to have the formula in place in a template so i can just drop the sales data in and it calculates how many of which product have been sold by who. But it only seems to work if i drop the data in and then rebuild the formula. Is it something around needing the exact number of rows it should be comparing, can i not just ask it to look for data in Column B for example? Hope someone can help! -- penri0_0 ------------------------------------------------------------------------ penri0_0's Profile: http://www.excelforum.com/member.php...o&userid=28947 View this thread: http://www.excelforum.com/showthread...hreadid=548970 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMPRODUCT Query
Can anyone help me with this one today?! -- penri0_0 ------------------------------------------------------------------------ penri0_0's Profile: http://www.excelforum.com/member.php...o&userid=28947 View this thread: http://www.excelforum.com/showthread...hreadid=548970 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMPRODUCT Query
Hi
You cannot use complete columns as range arguments in the Sumproduct formula. You could specify $B$2:$B$65535 for example, which is bound to cover all of your data when you paste in. You could keep all the data in one sheet, and use Filters to just show data for any given day - just a thought. -- Regards Roger Govier "penri0_0" wrote in message ... Why is it if i build a SUMPRODUCT formula around existing data in one of my sheets it works fine, but it won't 'pick up' data from a blank sheet which then has data dumped into it? i.e. Sheet 1 has 2 columns of data, product sold and by who. In Sheet 2 i have 10 columns (B to K) listing the different products available to sell. Column A has my salesperson's initials (all unique). Because i have to keep each days data on a seperate workbook i'd like to have the formula in place in a template so i can just drop the sales data in and it calculates how many of which product have been sold by who. But it only seems to work if i drop the data in and then rebuild the formula. Is it something around needing the exact number of rows it should be comparing, can i not just ask it to look for data in Column B for example? Hope someone can help! -- penri0_0 ------------------------------------------------------------------------ penri0_0's Profile: http://www.excelforum.com/member.php...o&userid=28947 View this thread: http://www.excelforum.com/showthread...hreadid=548970 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMPRODUCT Query
what happens is excel has the formula =@#$#@("A1:A100") then when you dump data it think that your cells ("A1:A100") have been moved not replaced what you need to do is lock the cell value e.g SOLUTION try this formula =@#$#@("$A$1:$A$100") dollar signs lock the cell no matter what quick guide click into your formula bar and click in between A and 1 and press F4 you will notice the dollar signs appear and press F4 until both appear a $ before the column will lock the column s if you drag the formula down the number will change just not the column and vice versa -- Zygan ------------------------------------------------------------------------ Zygan's Profile: http://www.excelforum.com/member.php...o&userid=34423 View this thread: http://www.excelforum.com/showthread...hreadid=548970 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMPRODUCT Query
Thanks both, I am making my 'look up' ranges in both columns i need to sum from absolute - (and not selecting columns!) - but i still get 0. Perhaps my formula is incorrect? In cell B3 i have: SUMPRODUCT(Sheet1!$C$1:$C$1000=A3)*(Sheet1!$L$1:$L $1000=B2) A3 contains one of my agents ID, B2 one of the the product types. Sheet 1 column C contains the selling agents IDs also, Sheet 1 column L contains the products (of which thewre is more than one type). Is it possible to sum using 2 lookups, as this would seem infinately more reliable! Roger - i can't use the filter for this piece of work, but thanks! -- penri0_0 ------------------------------------------------------------------------ penri0_0's Profile: http://www.excelforum.com/member.php...o&userid=28947 View this thread: http://www.excelforum.com/showthread...hreadid=548970 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMPRODUCT Query
Hi
You seem to be missing the outer set of brackets from your formula SUMPRODUCT((Sheet1!$C$1:$C$1000=A3)*(Sheet1!$L$1:$ L$1000=B2)) -- Regards Roger Govier "penri0_0" wrote in message ... Thanks both, I am making my 'look up' ranges in both columns i need to sum from absolute - (and not selecting columns!) - but i still get 0. Perhaps my formula is incorrect? In cell B3 i have: SUMPRODUCT(Sheet1!$C$1:$C$1000=A3)*(Sheet1!$L$1:$L $1000=B2) A3 contains one of my agents ID, B2 one of the the product types. Sheet 1 column C contains the selling agents IDs also, Sheet 1 column L contains the products (of which thewre is more than one type). Is it possible to sum using 2 lookups, as this would seem infinately more reliable! Roger - i can't use the filter for this piece of work, but thanks! -- penri0_0 ------------------------------------------------------------------------ penri0_0's Profile: http://www.excelforum.com/member.php...o&userid=28947 View this thread: http://www.excelforum.com/showthread...hreadid=548970 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMPRODUCT Query
=(COUNTIF(C1:C100,A3))*(COUNTIF(L1:L1000,B2)) try this one -- Zygan ------------------------------------------------------------------------ Zygan's Profile: http://www.excelforum.com/member.php...o&userid=34423 View this thread: http://www.excelforum.com/showthread...hreadid=548970 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMPRODUCT Query
Roger Govier Wrote: Hi You seem to be missing the outer set of brackets from your formula SUMPRODUCT((Sheet1!$C$1:$C$1000=A3)*(Sheet1!$L$1:$ L$1000=B2)) -- Regards Roger Govier Yes, sorry, that was just a typo - doesn't help you i know so apologies. Zygan - that returns 0 i'm afraid. I did check with a straight forward countif to make sure my formatting etc was exactly the same and thats ok. This has got me pulling my hair out! -- penri0_0 ------------------------------------------------------------------------ penri0_0's Profile: http://www.excelforum.com/member.php...o&userid=28947 View this thread: http://www.excelforum.com/showthread...hreadid=548970 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| changing query source | Excel Worksheet Functions | |||
| AHHH! Again | Excel Discussion (Misc queries) | |||
| AHHHH-Get Data from Multiple Excel workbooks | Excel Discussion (Misc queries) | |||
| I think its a sumproduct query? | Excel Discussion (Misc queries) | |||
| "Query cannot be edited by the Query Wizard" | Excel Discussion (Misc queries) | |||
Linear Mode
