Count cells that matchs criteria in Excel

Count cells match Excel criteria

COUNTIF Function:

=COUNTIFS(RangeHeading,">100",RangeHeading,"<500")
=COUNTIF(A1:A100,">500")     -- Count cells greater than value 500    
=COUNTIF(A1:A100,"jesus")    -- Count cells having jesus  
=COUNTIF(A1:A100,">" & B1 )  -- Count Cells greater than value in B1

Range Heading can be your table heading or column heading in the excel.

Count cells that matchs criteria in Excel was last modified: October 1st, 2017 by Justin Antony

Drop the tables and view with zero record count in sql server

 

DECLARE @tblschema varchar(250), @tblname varchar(250)
DECLARE @QRY Nvarchar(MAX)
DECLARE @VARIABLE Nvarchar(25)
CREATE TABLE #TMP1 (COL1 varchar(250))
DECLARE @TYPE int
DECLARE db_cursor CURSOR FOR  
select  case when table_type = 'VIEW' then 2 else 1 end, Table_Schema, Table_Name from INFORMATION_SCHEMA.TABLES
ORDER BY 1
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @TYPE, @tblschema, @tblname   
WHILE @@FETCH_STATUS = 0   
BEGIN   
SET @VARIABLE = ''
SET @QRY = N'select TOP 1  @VAR = ''X'' from ' + @tblschema + '.' + @tblname
EXECUTE sp_executesql @QRY, N'@VAR varchar(25) OUTPUT', @VAR = @VARIABLE OUTPUT 
IF ( @VARIABLE <> 'X') 
BEGIN
INSERT INTO #TMP1
select  case when @type = 1 then 'DROP TABLE  ' + @tblschema +'.'+ @tblname
when @type = 2 then 'DROP VIEW ' + @tblschema +'.'+ @tblname
END
END
FETCH NEXT FROM db_cursor INTO @TYPE, @tblschema, @tblname     
END   
SELECT *FROM #TMP1
DROP TABLE #TMP1
CLOSE db_cursor   
DEALLOCATE db_cursor
Drop the tables and view with zero record count in sql server was last modified: October 1st, 2017 by Justin Antony

DAX Aggregate and count Function

DAX Aggregate count Function

= Sum(Sales[Amt])

Four functions in count are
COUNT — Numeric Column
COUNTA — Count Any Column of any datatype
COUNTBLANK — Count empty cells
COUNTROWS — No of rows in a table

The Aggregate function ending with x calculate an expression (Second Parameter)
SUMX, AVERAGEX, COUNTX, COUNTAX, MINX, MAXX, DISTINCTCOUNT

cost:= SUMX(sales, sales[qty]* RELATED(Product[StdCost]))
— calculate Sum of sold quantity * standard cost from the product table for each row of sales (parameter 1)
Other words each parameter value 1 row the corresponding parameter 2 value will be calculated and return the aggregated result.

DAX Aggregate and count Function was last modified: October 1st, 2017 by Justin Antony