• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Freezing criteria ranges in Excel 2010 tables

GKlucsarits

New Member
Good day,


I'm trying to use Excel's new table functionality with COUNTIFS to summarize a large data set. Using cell references, I would be able to limit one criteria range while allowing the other two to increment in the destination cells as I drag the formula to the right.


For example: COUNTIFS(Table1[Column2],"=Fail",Table1[Column1],"<="&Sheet2!$C9,Table1[Column1],">="&Sheet2!$C9-6)


In the above, Column 1 represents a time stamp and Columns 2 through n are different test results. The data consists of many samples per day and my objective is to create a summary table by day showing the total failures for each test.


When I drag the above formula to the right I get:

COUNTIFS(Table1[Column3],"=Fail",Table1[Column2],"<="&Sheet2!$C9,Table1[Column2],">="&Sheet2!$C9-6)


I want the first criteria range to increment, which it is doing, i.e. going from Column2 to Column3, but I don't want the first column with the time stamps to increase.


Is there a way in which I can "freeze" the Column1 criteria range using the table features, or do I have to change it to "Sheet1!$A$1:$A$5000"?


Any help on the above, or an alternative suggestion would be greatly appreciated.


Cheers,


George
 
@George... I do not think you can free only a portion of structural references to table columns.


You can freeze everything in the formula by using Copy (CTRL+C) and Paste (CTRL+V) instead of dragging. Mind you, this only freezes structural references, not cell refs. So it might work for you.


You can overcome this limitation by using INDIRECT formula, but it would be too cumbersome. Instead you can just copy and paste the formulas in adjacent columns and then manually adjust the formula.
 
Hi Chandoo,


Thanks for the quick reply. I was afraid this would be the case; the challenge is that I have 36 columns to deal with.


Cheers,


George
 
@George

i suggest using the advanced options of the Find/Replace dialog box to quickly and efficiently modify your structured reference formulas instead of doing a cumbersome manual task of going into each formula and modify the pertinent reference giving you a headache


my 2 cents though

saludos
 
Back
Top