sumproduct formula and its limits?

[Guest]

Hi,

I am using the sumproduct formula more and more lately. I am curios as to
its limts. I know now (thanks Frank K.) that there is a limit to the length
of the formula (1254) and how many arays I can use. I have been using the
one below in a workbook to search a columns for matching data. I have two
columns of social security numbers that I paste the associated data into so I
can reconcile differences. Sometimes it works and sometimes not. For example
Dollar values yes, Zip codes no. And some address work and others do not.

Can someone fill me in on what I can or can't do with this formula?

=SUMPRODUCT(--(($C$2:$C$500)=($A4)),($D$2:$D$500))


Thanks,


Todd L.

 

[Frank Kabel]

Hi Todd
depends on what you're trying to do exactly and what is in these
columns. You current formula sums the values in column D. So you may
post the non working formula and also explain the used cell data

 

[Guest]

Thanks, I think its the data. I am retyping the data and it is working.
There must be some invisible inputs in the cells? Is there a way to get VB
or excel to delete anything unseen?

Todd

 

[Aladin Akyurek]

You can use

=SUMIF($C$2:$C$500,$A4,$D$2:$D$500)

instead of:

=SUMPRODUCT(--(($C$2:$C$500)=($A4)),($D$2:$D$500))

which is an expensive formula to invoke for cases of summing with a single
condition.

 

[hgrove]

Todd L. wrote
...

I have two columns of social security numbers that I paste th associated data
into so I can reconcile differences. Sometimes it works and sometime not. For
example Dollar values yes, Zip codes no. And some address work an others do
not.

Can someone fill me in on what I can or can't do with this formula?

=SUMPRODUCT(--(($C$2:$C$500)=($A4)),($D$2:$D$500))

...

First off, don't use unnecessary parentheses. They reduce clarity.

=SUMPRODUCT(--($C$2:$C$500=$A4),$D$2:$D$500)

Next, always give details about *HOW* something doesn't work
Presumably you're getting either zero values or errors when you believ
you should be getting nonzero numbers. If you're getting zeros yo
believe you shouldn't be, then the most likely (as in these explai

90% of newsgroup posters questions on this subject) causes are

1. C2:C500 are text and A4 is formatted numbers or vice versa, or
2. C2:C500 and A4 are all text but one or the other has trailin
spaces, or
3. D2500 are text rather than numbers.

While it's possible to construct elaborate formulas to deal wit
whatever's in these ranges, you're better off converting those range
to the same data type and eliminating all leading and trailing spaces
First eliminate the spaces using Edit > Replace. To force D2500 t
numeric, make sure the range has a number format other than Text, cop
a blank cell, and paste special onto D2500 as values and choosing th
Add operation. As for giving C2:C500 and A4 consistent data types, it'
obviously be easier to make sure A4's data type matches C2:C500's
Enter the formula

=COUNT(C2:C500)

If it returns 499, then all cells in the range are numbers. If i
returns zero, none are numbers. If it returns something in between
then enter the formula

=COUNT(C2:C500)=COUNTA(C2:C500)

If that returns TRUE, then all nonblank cells in the range are numbers
If it returns FALSE, then some cells are text and others are numbers
and you have a data cleansing chore. Once you've made C2:C50
consistent, make sure that A4 is also consistent.

In other words, your problem almost certainly has nothing to do wit
SUMPRODUCT and almost certainly everything to do with your data

 

[Guest]

The sum if works but only on the same numbers as the sumproduct. Also, I am
going to match text in the same columns later. I think if I can't clean up
the data this won't work. Retyping everything is more work than this is
worth.

Todd

 

[Guest]

Thank you. The tests you gave show I have lots of data problems. I guess I
better clean them up.

Thanks again!


Todd