Want to drag formula to new cells but only change one part

**argentraven** · 11-03-2015, 02:09 PM

I have this formula; =COUNTIFS(Tally!D188:D203, "X", Tally!E188:E203, ">=3")/COUNTIF(Tally!D188:D203, "X")*100

I would like to drag this down the column, row by row and have it advance by column on one part of the formula.. For example;

=COUNTIFS(Tally!D188:D203, "X", Tally!E188:E203, ">=3")/COUNTIF(Tally!D188:D203, "X")*100

=COUNTIFS(Tally!D188:D203, "X", Tally!F188:F203, ">=3")/COUNTIF(Tally!D188:D203, "X")*100

=COUNTIFS(Tally!D188:D203, "X", Tally!G188:G203, ">=3")/COUNTIF(Tally!D188:D203, "X")*100

=COUNTIFS(Tally!D188:D203, "X", Tally!H188:H203, ">=3")/COUNTIF(Tally!D188:D203, "X")*100

=COUNTIFS(Tally!D188:D203, "X", Tally!I188:I203, ">=3")/COUNTIF(Tally!D188:D203, "X")*100

I've tried using the $, but it does not seem to work.

**Debraj Roy** · 11-03-2015, 02:11 PM

try something like this..

=COUNTIFS(Tally!D188:D203, "X", Tally!E$188:E$203, ">=3")/COUNTIF(Tally!$D$188:$D$203, "X")*100

**Jonmo1** · 11-03-2015, 02:14 PM

Columns don't incriment when formulas are dragged up or down.
As well as Rows don't incriment when formulas are dragged left or right.

Try this (you won't actually see the range change, but it will be properly referenced as you drag down)

=COUNTIFS(Tally!D$188:D$203, "X", OFFSET(Tally!E$188:E$203,0,ROWS(A$1:A1)-1), ">=3")/COUNTIF(Tally!D$188:D$203, "X")*100

**José Augusto** · 11-03-2015, 02:18 PM

Hi
Use =COUNTIFS(Tally!$D$188:$D$203,"X",OFFSET(Tally!$D$188:$D$203,0,ROW($A1)),">=3")/COUNTIF(Tally!$D$188:$D$203,"X")*100

Formula:

Please Login or Register  to view this content.

and copy down.

**argentraven** · 11-03-2015, 02:22 PM

Perfect!!! Thank you so much! You saved me hours of manual entry. You should get a raise!

**Jonmo1** · 11-03-2015, 02:35 PM

You're welcome.

Originally Posted by argentraven

You should get a raise!

I'm sure the Forum Admins would gladly give my a 50% raise.

Want to drag formula to new cells but only change one part

LinkBack

Thread Tools

Rate This Thread

Display

Want to drag formula to new cells but only change one part

Re: Want to drag formula to new cells but only change one part

Re: Want to drag formula to new cells but only change one part

Re: Want to drag formula to new cells but only change one part

Re: Want to drag formula to new cells but only change one part

Re: Want to drag formula to new cells but only change one part

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Change part of formula in multiple cells

[SOLVED] Change part of a VLOOKUP formula across mulitple cells and worksheets

[SOLVED] I want my criteria to change when I drag a formula

[SOLVED] Drag formula, change sheet

[SOLVED] How to drag / copy to 1000 rows, leaving one part of the formula static?

Excel 2007 : Drag formula over cells and change a specific part of it

How do I keep letters the same and change the # for drag formula

Bookmarks

Bookmarks

Posting Permissions