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
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