Sumproduct forumla for complex sum.

[DocBrown]

I think I'm between a rock and a hard place. On one hand a UDF allows me to
calculate the sum I want. But on the other, a UDF on the WS causes another
macro called from Worksheet_change to fail with Error 1004.

See my other thread called 'UDF interaction with other macro causes Error
1004'

There's another feature that I want to add that also requires a UDF. So now
what?

Any ideas?

 

[Bernie Deitrick]

Doc,

The LOOKUP or INDEX double array requirement prevents a single cell formula,
but you can use a column of helpers.

In G1, enter

=IF(AND(E1<>"",F1=""),INDEX($B$1:$B$7,MATCH(E1,$A$1:$A$7,FALSE)),"")

and copy down to G7.

Then use

=SUM(F1:G7)+SUMIF(A1:A7,"",B1:B7)

to give 1,346.

HTH,
Bernie
MS Excel MVP

 

[DocBrown]

Hi Bernie,

Thanks for your input. Using the helper cells is a good idea. It makes the
solution much simpler. Because of my requirements. I needed to use a
different formula. The items in A can appear multiple times. But using MATCH
only picks up the first one in A.

Here's what I did.
In G1:G7:
=IF(AND(E1<>"",F1=""),SUMIF($A$1:$A$7,E1,$B$7:$B$7),"")

In the total sum cell I used your function.

Thanks for the new approach.
John