How the COUNTIF Function Works-2

The COUNTIF Function's Syntax. In Excel, a function's syntax refers to the layout of the function and includes the function's name, brackets, and arguments. The syntax for the COUNTIF function is: = COUNTIF ( Range, Criteria ). The COUNTIF Function's Arguments.The function's arguments tell the function what condition we are testing for and what range of data to count when the condition is met. 

Range - the group of cells the function is to search. Criteria - this value is compared with the data in the Range cells. If a match is found then the cell in the Range is counted. Actual data or the cell reference to the data can be entered for this argument. You can do a COUNTIF on the outcome of some Data or Election result. Example: In column A, enter "Party" and type APC, SDP,NRC and PDP.

SUMIF condition

in cells A2 to A5. Replicate A2:A5 on A6:A9. Column B is Votes Counted and in cells B2:B9 and enter 100,345,546,768,34,987,175 and 40 respectively with total votes at 3000. Repeat A2:A5 on A12:A15 and in cell B12, type this formula. SUMIF($A$2: $B$9,A12,$B$2: $B$9) and drag down to cell B15. Add up this values to confirm that is gives you same 3000. When u v large data, with SUMIF, its just fun.

How the COUNTIF Function Works-1

The COUNTIF function combines the IF function and COUNT function in Excel. This combination allows you to count the number of times specific data is found in a selected group of cells. The IF portion of the function determines what data meets the specified criteria and the COUNT part does the counting. 

We will use a set of data records and the COUNTIF function to find the number of Sales Reps  who have more than 250 orders for the year. Entering the Tutorial Data: The first step to using the COUNTIF function in Excel is to enter the data. Enter the data below into cells A1 to C5 of an Excel worksheet. The COUNTIF function and the search criteria (greater than 250 orders) will be added to row 6 below the data. Now let's do this 2geda. In cell A1, enter Sales Rep, A2=Dapo, A3=Jude A4=Austin, A5= Ade. B1=Regions, B2=North, B3=South, B4=East and B5=West. C1=#Orders, C2=265, C3=228, C4=267 and C5=261. In cell B6, type >250 and in cell C6, type COUNTIF(C2:C5,B6). If well recorded, C6 will return a value of 3 because the Orders by Sales Rep that is more than 250 are just 3. You can change the sign in C6 to <250...what do you have please? CIAO

How the SUMIF Function Works-1

I had earlier stated that the If function also works with other functions to achieve desired results. You can use If function with "AND","OR",COUNTIF, SUMIF,AVERAGEIF,SUMIFS, COUNTIFS, AVERAGEIFS. Now let's see how it works with the SUM Function. The SUMIF function combines the IF function and SUM function in Excel. This combination allows you to add up those values in a selected range of data that meets specific criteria. 

The IF portion of the function determines what data meets the specified criteria and the SUM part does the addition. Normally, SUMIF is used with rows of data called records. In a record, all of the data in each cell in the row is related - such as a company's name, address and phone number. SUMIF looks for the specific criteria in one cell or field in the record and if it finds a match, it adds that data or data in another specified field in the same record. Although it is possible to just type the SUMIF function into a cell in a worksheet, many people find it easier to use the function's dialog box to enter the function. Steps. Click on cell A1 to make it the active cell. 

This is where we will enter the SUMIF function.Click on the Formulas tab. Click on the Math & Trig icon on the ribbon to open the function drop down list. Click on SUMIF in the list to bring up the SUMIF function's dialog box. The data that we enter into the three blank rows in the dialog box will form the arguments of the SUMIF function. These arguments tell the function what condition we are testing for and what range of data to sum when the condition is met. CIAO!

How the IF Function works-4

Yesterdays traffic in Lagos is what Prof. Wole Soyinka would call "A mysterious millipede, moving towards a waling wake-keep". Permit me to liken yesterday's traffic experience to what i faced when i started my data analysis career years back in the university. By chance (knowledge of Excel) I found myself handling student's result (mine inclusive). My relationship with my employer was based firstly on trust, afterwards was the fact that i knew excel inside-out as i could compute an entire session without touching the mouse. 

I was good with shortcuts and that earned me more respect than others 'cos i deliver in lesser time, all the time. I ended up automating the manual result, i saved students from manually computed extra years (due to errors), i saved jobs for those with outstanding results and I got myself employed by my department whilst a student and stopped depending on my parents for monthly allowee. I became more responsible to people's lives (results) and as a result, never sat for any aptitude test (with serious mind) because i already had a job right before graduating. In 2003 (still a student), I sold the automated Excel result for N150k. I wanted to die!

But i didn't die bcos i knew "IF" i die, someone else will spend the money and bcos i also knew the "IF Function", i sold same application and made more money. With the combination of majorly the "IF" and "VLOOKUP" function, i delivered a fully automated student result that generates result slips and also used same concept to build a salary payroll package for N250k in 2005. That was when my focus shifted from accounting to IT, with flair for applications and databases. The "IF" function is the most powerful 1st function to learn in Ms Excel and the mastery of same will give you needed joy and some level of peace + confidence at your workplace. It is simple to know + use and I would like to teach you how, "IF" and only " IF" you want to know it. Excel with Excel. CIAO.

Welcome Onboard

ExcelGist is a platform where you can learn and discuss Microsoft Excel tricks for Basic, Intermediate and Advance users. Join in now to Excel with Excel