SUMPRODUCT Query

[penri0_0]

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]

Can anyone help me with this one today?

 

[Roger Govier]

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.

 

[Zygan]

what happens is excel has the formula =@#$#@("A1:A100")
then when you dump data it think that your cells ("A1:A100") have bee
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 F
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 formul
down the number will change just not the column and vice vers

 

[penri0_0]

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
contains the products (of which thewre is more than one type).
Is it possible to sum using 2 lookups, as this would seem infinatel
more reliable!

Roger - i can't use the filter for this piece of work, but thanks

 

[Roger Govier]

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

 

[Zygan]

=(COUNTIF(C1:C100,A3))*(COUNTIF(L1:L1000,B2))

try this on

 

[penri0_0]

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

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!