sumproduct in code

What I am trying to accomplish is this:
I am trying to use a for loop to create a table that generates a column
(aa). Based on the cells in AA, I want sumproduct (in column ab) to perform
add up the sums in another table (cause.column(3)) where the value in
causecats (which is the first column in the cause table) is equalt to the
value in AAr+1.

The values in AAr+1 are populated from a review of the values in causecats.

The code I am trying is this.
Cells(r + 1, "ab").Formula = "=sumproduct((" & causecats.Address & "=" & _
Cells(r + 1, "aa").Address & ")*(" & cause.Columns(3).Address & "))"

From my experimentation, I can not get the sumproduct to work unless I put
the value I am looking for within the first array in quotes. In other words,
I can not say
"=sumproduct((array1=a3)*(array2)"
This appears to only work if I put an actual value in place of a3 and place
it in quotes.
I think that is my problem above. Is there a way around it?

TIA

I don't have the solution for you, but maybe this will help.

When I have trouble plopping a formula into a cell, I'll change it to text.

Cells(r + 1, "ab").Formula = "sumproduct((" & causecats.Address & "=" & _
Cells(r + 1, "aa").Address & ")*(" & cause.Columns(3).Address & "))"

(I dropped the leading equal sign.)

Then I go back to the worksheet and see what happens when I add the equal sign
back.

Usually, it gets a lot easier to solve the problem.



Papa Jonah wrote:
>
> What I am trying to accomplish is this:
> I am trying to use a for loop to create a table that generates a column
> (aa). Based on the cells in AA, I want sumproduct (in column ab) to perform
> add up the sums in another table (cause.column(3)) where the value in
> causecats (which is the first column in the cause table) is equalt to the
> value in AAr+1.
>
> The values in AAr+1 are populated from a review of the values in causecats.
>
> The code I am trying is this.
> Cells(r + 1, "ab").Formula = "=sumproduct((" & causecats.Address & "=" & _
> Cells(r + 1, "aa").Address & ")*(" & cause.Columns(3).Address & "))"
>
> From my experimentation, I can not get the sumproduct to work unless I put
> the value I am looking for within the first array in quotes. In other words,
> I can not say
> "=sumproduct((array1=a3)*(array2)"
> This appears to only work if I put an actual value in place of a3 and place
> it in quotes.
> I think that is my problem above. Is there a way around it?
>
> TIA

--

Dave Peterson

Just for reassurance, that shouldn't be a problem. If it is, it would
indicate you are not constructing your formula properly and Dave has given
you some hints on how to test that (I normally use the immediate window -
but we all have our own methods).

--
Regards,
Tom Ogilvy

"Papa Jonah" <[email protected]> wrote in message
news:[email protected]...
> What I am trying to accomplish is this:
> I am trying to use a for loop to create a table that generates a column
> (aa). Based on the cells in AA, I want sumproduct (in column ab) to
perform
> add up the sums in another table (cause.column(3)) where the value in
> causecats (which is the first column in the cause table) is equalt to the
> value in AAr+1.
>
> The values in AAr+1 are populated from a review of the values in
causecats.
>
> The code I am trying is this.
> Cells(r + 1, "ab").Formula = "=sumproduct((" & causecats.Address & "="
& _
> Cells(r + 1, "aa").Address & ")*(" & cause.Columns(3).Address & "))"
>
> From my experimentation, I can not get the sumproduct to work unless I put
> the value I am looking for within the first array in quotes. In other
words,
> I can not say
> "=sumproduct((array1=a3)*(array2)"
> This appears to only work if I put an actual value in place of a3 and
place
> it in quotes.
> I think that is my problem above. Is there a way around it?
>
> TIA

If I used the immediate window, I'd still have to paste it into a cell to see
where I messed up my commas/parentheses <bg>.

Tom Ogilvy wrote:
>
> Just for reassurance, that shouldn't be a problem. If it is, it would
> indicate you are not constructing your formula properly and Dave has given
> you some hints on how to test that (I normally use the immediate window -
> but we all have our own methods).
>
> --
> Regards,
> Tom Ogilvy
>
> "Papa Jonah" <[email protected]> wrote in message
> news:[email protected]...
> > What I am trying to accomplish is this:
> > I am trying to use a for loop to create a table that generates a column
> > (aa). Based on the cells in AA, I want sumproduct (in column ab) to
> perform
> > add up the sums in another table (cause.column(3)) where the value in
> > causecats (which is the first column in the cause table) is equalt to the
> > value in AAr+1.
> >
> > The values in AAr+1 are populated from a review of the values in
> causecats.
> >
> > The code I am trying is this.
> > Cells(r + 1, "ab").Formula = "=sumproduct((" & causecats.Address & "="
> & _
> > Cells(r + 1, "aa").Address & ")*(" & cause.Columns(3).Address & "))"
> >
> > From my experimentation, I can not get the sumproduct to work unless I put
> > the value I am looking for within the first array in quotes. In other
> words,
> > I can not say
> > "=sumproduct((array1=a3)*(array2)"
> > This appears to only work if I put an actual value in place of a3 and
> place
> > it in quotes.
> > I think that is my problem above. Is there a way around it?
> >
> > TIA

--

Dave Peterson