Help

I suggest this one, see if it's the same result, i tried with some dummy data, but maybe i'm leaving something out.

=-SUM((Raw_Data!$J$2:$J$12600=$I17)*((Raw_Data!$G$2:$G$12600=$B$6)+(Raw_Data!$G$2:$G$12600=$B$7)+(Raw_Data!$F$2:$F$12600=$A$6)+(Raw_Data!$H$2:$H$12600=$D$6,1,0))*(Raw_Data!AY$2:AY$12600/1000))

I hope that does it.

Juan Pablo

Can you put this formula to the right of your data in the Raw_Data worksheet and changing it to to read (maybe in cell BA1, assumes that your original formula was in worksheet named "Sheet1"):

=-Raw_Data!$J$2=Sheet1!$I17*Raw_Data!$G$2=Sheet1!$B$6*Raw_Data!AY$2/1000-Raw_Data!$J$2=Sheet1!$I17*Raw_Data!$G$2=Sheet1!$B$7*Raw_Data!AY$2/1000-Raw_Data!$J$2=Sheet1!$I17*Raw_Data!$F$2=Sheet1!$A$6*Raw_Data!AY$2/1000-Raw_Data!$J$2=Sheet1!$I17*Raw_Data!$H$2=Sheet1!$D$6*Raw_Data!AY$2/1000

Then, in Sheet1, you could sum up column BA. This will eliminate the problem of Excel having to evaluate the entire Raw_Data spreadsheet each time you make a change.

Do you think this would work for you?

Regards,
BarrieBarrie Davidson

Juan, this worked except for one thing. There are four "choices" in column G. When B6 is filled and not 7, 8 and 9, it adds up the choice I entered and two of the others as well. Why?

Thanks in advance.

I don't know... i saw one thing in the formula

=-SUM((Raw_Data!$J$2:$J$12600=$I17)*((Raw_Data!$G$2:$G$12600=$B$6)+(Raw_Data!$G$2:$G$12600=$B$7)+(Raw_Data!$F$2:$F$12600=$A$6)+(Raw_Data!$H$2:$H$12600=$D$6))*(Raw_Data!AY$2:AY$12600/1000))

In the $D$6 there was a ,1,0 left from the IF statement that you had, i erased it... hope that was the error.

Juan

I ran some more queries. For some reason the calculation automatically pulls two of the criteria.

I like the formula, but I'm perplexed why this is happening. Any other suggestions?

Re: Juan

Don't know..... can you send it to me so i can take a look at it ? it's better to work with the real data...

Juan Pablo