Knowledge Sharing

In this post I would like to talk about two functions COUNTIF and SUMIF . these two functions will be extremely helpful for us to aggregate details very easily. see the examples given below to identify the use. Sheet value: Fig 1 Find the sum of sales for each sales person. We can use SUMIF function to accomblish this requirement. SUMIF expects three parameter as shown in figure 2. range is the total detailed area where the function has to search for the details, criteria is the value that should find in the given range and the sum range is the range of values to be aggregated for the selected criteria. Fig 2 Formula looks like this =SUMIF( D$2:E$10 , H$2 , E2:E10 ) and the result is Name Jithesh a b c Sales Amount 95274 476 545 4345 Same way, to find the number of sales done by the person, we can make use of the function countif, an inbuilt function of MS-Excel. It expects two parameter, first

OPENQUERY Executes the specified pass-through query on the specified linked server. This server is an OLE DB data source. OPENQUERY can be referenced in the FROM clause of a query as if it were a table name. OPENQUERY can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement. This is subject to the capabilities of the OLE DB provider. Although the query may return multiple result sets, OPENQUERY returns only the first one. OPENQUERY does not accept variables for its arguments. OPENQUERY cannot be used to execute extended stored procedures on a linked server. However, an extended stored procedure can be executed on a linked server by using a four-part name Any call to OPENDATASOURCE, OPENQUERY, or OPENROWSET in the FROM clause is evaluated separately and independently from any call to these functions used as the target of the update, even if identical arguments are supplied to the two calls. In particular, filter or join conditions applied on the r