Sumproduct (probably easy) question

[Keith R]

I have two worksheets in my workbook. I'm trying to get a simple sumproduct
based, but I keep getting #Num! values, and can't get my formula to work.
I'd appreciate any suggestions. using XL2003.

Sheets: Raw Data, Main

In Raw Data (about 25K rows)
A B
Item Owner

In Main: comparison Item in C, Owner in Row 1 of that column (with fake data
indicating what I want the formula to return in I2
A B C..... I J K
1 Misc Misc Item OwnerA OwnerB OwnerC
2 Car 2

I'm trying to count the number of each item that each owner has;

Everything I've seen suggests that the proper syntax is:

=SUMPRODUCT((('Raw Data'!A:A)=$C2)*(('Raw Data'!B:B)=$I$1)*1)

but that isn't returning a result (other than #NUM!)

Are there issues with using ranges on other sheets? Or using the entire
column instead of a discrete range (e.g. will blank rows kill it)?

Thanks!
Keith

 

[Don Guillett]

You can NOT use sumproduct with entire columns.
use a1:a100
or defined named ranges

 

[Rick Rothstein \(MVP - VB\)]

You can't use entire column references in the SUMPRODUCT formula in versions
less than XL2007. Since you are using XL2003, you will have to specify the
full range...

=SUMPRODUCT((('Raw Data'!A1:A25000)=$C2)*(('Raw Data'!B1:B25000)=$I$1)*1)

You don't need that *1 in your formula as you are already multiply the other
expressions.

Rick

 

[Keith R]

Don- thank you for the clarification- I wasn't aware of the whole-column
limitation of Sumproduct. I've adjusted my formula, and now get #Value! in a
cell that I have confirmed should actually have a count (using "=a1=B17"
type checking to make sure I have exact matches). My revised formula, which
covers a large range just for testing purposes, is:

=SUMPRODUCT(('Raw Data'!$A2:$A30000=$C2,'Raw Data'!$B2:$B30000=K$1)*1)

I'm one step closer, since #Value! is better than #Num!, but I'm still
making quasi-random changes in the hopes that I'll magically come across the
right syntax. Any additional assistance you or other readers could provide
would be greatly helpful.

Thanks!
Keith

 

[Rick Rothstein \(MVP - VB\)]

Don- thank you for the clarification- I wasn't aware of the whole-column

limitation of Sumproduct. I've adjusted my formula, and now get #Value! in
a cell that I have confirmed should actually have a count (using "=a1=B17"
type checking to make sure I have exact matches). My revised formula,
which covers a large range just for testing purposes, is:

=SUMPRODUCT(('Raw Data'!$A2:$A30000=$C2,'Raw Data'!$B2:$B30000=K$1)*1)

I'm one step closer, since #Value! is better than #Num!, but I'm still
making quasi-random changes in the hopes that I'll magically come across
the right syntax. Any additional assistance you or other readers could
provide would be greatly helpful.

Try your formula this way...

=SUMPRODUCT(('Raw Data'!$A2:$A30000=$C2)*('Raw Data'!$B2:$B30000=K$1))

Rick

 

[PCLIVE]

Maybe this:

=SUMPRODUCT(--('Raw Data'!A2:A30000=$C2),--('Raw Data'!B2:B30000=$K$1))

You switched to K1 in your new formula. I'm assuming you meant to do that.

Regards,
Paul

--

 

[Keith R]

To all who contributed, a mighty thank you. My final formula turned out to
be:

=SUMPRODUCT(('Raw Data'!$A$2:$A$30000=$C2)*1,('Raw
Data'!$B$2:$B$30000=K$1)*1)

Best,
Keith

 

[PCLIVE]

Didn't my suggestion product the same results?

 

[Keith R]

I think that it would have (I'm at home, the workbook is at work)- I think
my key problem before is that I thought only one of the sumproduct
conditions needed to be "converted" into a number, whereas your solution and
my final formula address both conditions.

I saw your post after I got the one expression working, and I didn't want to
risk further user error- I apologize for not verifying all proposed
solutoins.


Thanks,
Keith