Sumproduct

[Edge118]

Hi, I am using the SUMPRODUCT function to count cells, but I have
problem...

=SUMPRODUCT((Description!B2:B11=A3) * (Description!A2:A11=$B$1))

As you can see I use Description!B2:B11 because my data range is o
another worksheet. The problem I run into is, the table of data is no
going to be one length and it will change regularly as new data i
entered, and I find that when I change the range to include empty cell
it gives me #N/A.

(Example: =SUMPRODUCT((Description!B2:B12=A3)
(Description!A2:A11=$B$1)) will result in "#N/A" since cell B12 i
empty)

What I want to be able to do is make the range the entire B column, an
not get the "#N/A" error message in the result cell. Is there anywa
to accomplish this?

Thanks for your help.

R

 

[Peo Sjoblom]

It's not because it is empty it's because you use different size
use

=SUMPRODUCT((Description!B2:B12=A3) * (Description!A2:A12=$B$1))

i.e. B2:B12 need to have the same size as A2:A11


--
For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

 

[Edge118]

I see what you are saying and that helps. Why doesn't it work when
have:

=SUMPRODUCT((Description!B:B=A4) * (Description!A:A=$B$1))

This selects the entire column, and since they are the same lengt
technically, why does it give me #NUM!

Any idea?

Thanks for your help!

R

 

[Peo Sjoblom]

Because you can't use a whole column in array formulas, you would need
A1:A65535

--
For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom