How to use Ms-Excel function SUMIFS()?

SUM and SUMIF function are very common and useful for the people who deal with Ms-Excel. Ms-Excel has a wide range of functions. Recently I learn about SUMIFS() function.

SUMIFS was introduced with MS-Excel 2007 and it works similar to SUM and SUMTIF i.e. it returns sum total of rows. The best thing about SUMIFS is it gives us functionality to apply multiple filtering criteria. It mainly has three parameters.

SUMIFS(sum_range, criteria_range1, criteria)

In the sum_range parameter we need to supply the cell range or column for which we want to return sum total.. If your entire data is in 5 rows and 5 columns (A to E column) and you want to return Sum of 5th column then you can pass E1:E5 as sum_range parameter.

In the criteria parameter we need to supply the condition or data that we are searching for. The best thing about criteria parameter is it accepts string and logical expression(>, <, =).

Let us understand it with a basic example. We have following data in the excel sheet.

Now if we have to find out sum total of Quantity Vendor Samsung supplies. We can use SUMIFS() function. We can pass the three parameters as discussed above.

=SUMIFS(E3:E18,$C$3:$C$18,"=Samsung")

This will return a sum total of 190 So Samsung is the vendor who is supplying 190 items in quantities.

Now, let us find out the sum total of quantity that Samsung supply which costs above $200

=SUMIFS(E3:E18,$C$3:$C$18,"=Samsung",D3:D18,">200")

This will return a sum total of 156. So Samsung is the vendor for 156 items which costs more than $200.

Now, let us find out sum total of products that Samsung supply which costs above $200 and item quantity is more than 80.

=SUMIFS(E3:E18,$C$3:$C$18,"=Samsung",D3:D18,">200",E3:E18,">80")

This will return a sum total of 90. So Samsung is the vendor who is supplying 90 items of for two products which costs more than $200 and has quantity more than 10.

So as you can see SUMIFS() function can accepts multiple criteria. It supports up to 27 range and criteria.

The important thing to consider about SUMIFS function is that the range should be same in all multiple criteria that we apply.

How to use Ms-Excel function COUNTIFS()?

COUNT and COUNTIF function are very popular and useful for the people who deal with Ms-Excel. Ms-Excel has a wide range of functions. Recently I learn about COUNTIFS() function.

COUNTIFS was introduced with MS-Excel 2007 and it works similar to COUNT and COUNTIF i.e. it returns number of rows. The best thing about COUNTIFS is it gives us functionality to apply multiple filtering criteria. It mainly has two parameters.

COUNTIFS(Range, criteria)

In the range parameter we need to supply the cell range of data that we are searching for. If your entire data is in 5 rows and 5 columns then you can pass A1:E5 as range parameter. In the criteria parameter we need to supply the condition or data that we are searching for. The best thing about criteria parameter is it accepts string and logical expression(>, <, =).

Let us understand it with a basic example. We have following data in the excel sheet.

Now if we have to find out number of items Vendor Samsung supplies. We can use COUNTIFS() function. We can pass the two parameters as discussed above.

=COUNTIFS(B3:E18,"=Samsung")

This will return a row count of 3. So Samsung is the vendor for three products.

Now, let us find out how many products Samsung supply which costs above $200

=COUNTIFS($C$2:$C$18,"=Samsung",$D$2:$D$18,">200")

This will return a row count of 2. So Samsung is the vendor for two products which costs more than $200.

Now, let us find out how many products Samsung supply which costs above $200 and quantity is more than 10.

=COUNTIFS($C$2:$C$18,"=Samsung",$D$2:$D$18,">200",$E$2:$E$18,">50")

This will return a row count of 2. So Samsung is the vendor for two products which costs more than $200 and has quantity more than 10.

So as you can see COUNTIFS() function can accepts multiple criteria. It supports up to 27 range and criteria.

The important thing to consider about COUNTIFS function is the range should be same in all multiple criteria that we apply.

Biometric ATM in India

Banking is very much part of people’s life today. We all are using banking services such as ATMs, net banking, online payment etc. which adds convenience to our life.  Technologies in banking sector have made our life very easy. The change in technology in banking services do impacts us. It changes the method how we are using banking tools and services.

The introduction of Biometric ATMs is latest entrant in the banking services. We all have seen and used traditional ATMs which accept our debit or credit card and we enter our PIN code and use banking services to deposit money, withdraw cash, check balance, request for check books etc. We truly trust the bank and ATM that we are using at that point of time.

As the technology evolves, it also brings some sort of risk with it. You might have read or know about ATM frauds, duplication of ATM cards, ATM skimming, unauthorized withdrawal of money. You also might have seen people and groups of thieves involve in such crimes. Banks and Bank regulator always try to bring and adopt technology which brings safety and security to the customer.

The Biometric ATM has been introduced to safeguard customers from all the frauds and unauthorized access to bank account.

How does Biometric ATM work? 

Biometric ATM works similar to traditional banks but it does not ask for a numeric password. Biometrics ATMs have attached scanner or sensor where you put your finger or palm. Your finger print acts like a password. Your finger print is then matched with the finger print stored in the bank centralized database. If it matches you are authorized to get access to ATM and your account.

To use Biometrics ATM the first steps is to visit your bank or branch and provide your finger prints to the bank so that it can be registered in the bank’s centralized system.

Finger prints are popular but a person Biometrics data can be divided into three categories:

Physical Biometric data

1.       Finger

2.       Hand or Palm

3.       Finger Print

Geometry Biometric data

1.       Eye

2.       Iris

3.       Retina

Behavioral Biometric data

1.       Voice

2.       Signature

3.       Typing

4.       Pointing

It depends on the bank what biometric data they want to store for their customer.  

Once your biometric data is registered you can use the Biometrics ATMs provided by your banks and use banking services.

(Image Source: CNN)

Biometric ATM Presence in India

Software vendors have developed biometrics solutions for the ATMs. FSS (Financial Software and System), CMC Limited (FACTS – Fingerprint Analysis and Criminal Tracking System), Axis technologies (Biometrics Retrofit Kit) are some of the leading vendors who have developed the solutions in biometrics space.

In India the first Biometric ATM was setup by ICICI bank in Andhara Pradesh in 2005. Today, State bank of India, Punjab National bank, Axis bank, Catholic Syrian bank, Grameen Vikas Bank all have setup Biometric ATMs.

One of the strong reasons these banks are setting up Biometric ATMs is rural population in India. People in rural and hill areas find it difficult to remember numbers as their PIN code. Biometric ATMs help them to overcome this problem and at the same time it adds safety and security in the banking process.

Biometric ATMs are very popular in Europe, Japan and Middle east. This technology is setting pace in India.

How to calculate Team utilization index?

Joey has been promoted as a team lead and obviously he is very happy today. He was aiming this profile change since few years. With the new role comes new responsibilities and Joey has been training and acquiring team lead skills for quite some time.

A month later, I saw Joey coming to my desk. "Do you have some time? I have some questions." – Joey said”. “Sure – I replied”.

Joey: I am working on some reports for the management and I did not understand the team utilization. How can I calculate my team utilization?

It is all easy. Relax and pull up a chair. How many team members you have in your team - I said.

Joey: We are a team of five people including me.

Does your team uses any tool, template to record their effort hours? – I said.

Joey: Oh yes, we use a web tool to record all our effort hours. We enter how many hours we have spent doing a particular activities.

Great, then pull all the effort hours that your team has entered in that tool for the month. – I said.

Do you maintain leave records for your team, no. of days your team has to work in a month? No. of hours your team has to work in a day time?

Joey: Yes we have a excel spreadsheet that I maintains for all the resources.

Good, so you have all the inputs ready with you to calculate your team utilization. – I said.

You need to first calculate “Total available Hours” of your team. This can be done by multiplying No. of working hours per person of your team into No. of working days available in the month. Then multiply it by No. of team members you have in the team.

Total available hours = No. of hours per head X No. of working days in the month X Team size

Total available hours = 8 X 22 X 5 = 880 hours.

Next, you have to calculate “Total Leave hours” of your team. This can be done by looking at number of total leave taken by your team and multiplying it by No. of working hours per person.

So, if your team has taken 10 leaves in the month then your Total Leave hours are:

Total Leave hours = No. of hours per head * No. of total leaves taken

Total Leave hours = 8 * 10 =80 hours.

Next, you have to calculate “Actual available hours”. This is done by subtracting Total leave hours from Total hours available.

---

What is a Project Charter?

What is a Project Management Plan?

What are Organizational Structure?

What is Brainstorming?

PM: Do you know your Project Cost?

PM: How accurate is your Project Cost Estimates?

How to calculate Team utilization index?

How to motivate your Project Team?

PM: Do you know your Communication Channel?

What is XMR Chart?

What is Plan Procurement Management?

---

Actual available hours = Total available hours – Total Leave hours

Actual available hours = 880 – 80 =800 hours

Next, you have to take “Total Effort hours” that your team has spent doing all activities in the project. Let‘s say it comes around 750 hours.

So to calculate your team utilization you have to divide Total Effort hours by Actual available hours.

Team utilization percentage = (Total Effort hours / Actual available hours) * 100

Team utilization percentage = (750/800)*100 = 93.75 percent.

So, you can report that 93.75% is your team utilization. Now, as a team lead you to figure out where does the rest of hours goes to.

Joey: Oh wow, you made it so simple for me. Thanks a ton.

You are always welcome dude – I said looking at his face which was smiling all the ways.