Formula change

[RodneyW]

In row 40 I have this formula in one of my cells:

=SUM((W5:W74=1)*($D5:$D74="KXFX")*(X5:X74))

A very similar formula is in most cells across about 20ish columns.

I need to take the formula across all columns and either copy/paste or drag it down to row 41. Doing so changes W74 to W75, $D74 to $D75 and X74 to X75. I need it to stay at W74, D74 and X74. When I put the $ in front of the 74, it screws up the results and shows #VALUE.

How can I copy the formulas from row 40 and place them in row 41 without manually doing it in every cell?

Thanks

 

[cstimart]

Is it possible that you should add $'s in front of the other W's and X's?

=SUM(($W$5:$W$74=1)*($D5:$D74="KXFX")*($X$5:$X$74))

 

[RodneyW]

Adding the $ doesn't work. No matter which I add it to, it changes the value to #VALUE. Also, adding the $ should create a constant reference to the specific cell(s). This doesn't work because from time to time, I will be adding rows between 5 and 74 so I need the reference to 74 to change to whatever the appropriate number is based on how many rows I add.

 

[lenze]

Shouldn't you be using SUMPRODUCT??

=SUMPRODUCY(--(W5:W74=1)*($D5:$D74="KXFX")*(X5:X74))

lenze

 

[RodneyW]

Lenze... Thanks for your reply.

I copy/pasted your code into the cell and it produces the same result/value as the code I am currently using.

However, when I drag down or copy/paste, it produces the same result as described in my original post.

Is what I want to do possible or do I need to do it by hand... there's about 20 separate sheets, each with a couple dozen cells that would need to be changed so doing it by hand is not preferred.

 

[Aladin Akyurek]

Does this...

=SUMPRODUCT(--(W5:$W$74=1),--($D5:$D$74="KXFX"),X5:$X$74)

satisfy?

 

[RodneyW]

Looks like it works perfect. Thanks!

 

[Aladin Akyurek]

Looks like it works perfect. Thanks!

Great. Thanks for providing feedback.