Sunday, May 10, 2020

Working with Cell referencing (Date Function)


Suppose, we have individual cell consists individual values of year, month and day.

Like the below image,




From those individual values of the year, month and day, we want to create a valid date format by referencing those cells. 

To do that, we will choose the blank cell below the label DATE FORMAT and start typing “=Date” and then click on the suggested DATE function like previously done but here we will not input values manually.

We will select those cells A2, B2 and C2 respectively and these values will also be separated with a comma (keep in mind) and after completion of referencing those cells just press enter of your keyboard and you will get a valid date format as a result.

Look at the below GIF carefully to get a clear concept about it.


That’s all for today. Stay with tutorialabc and if you have any query or suggestions for us, please feel free to contact us via Contact page of this website

General Date Function in Excel


Date function is widely used function in Excel for serving several purposes. Purpose including, creating an invoice for a company, student report card or for a data entry job, you need to work with Dates. So, it is one of the basic things of a learner to know, how to use Date function in Excel.

So, let’s proceed to that,

In brief, Date function makes a legitimate date format of a year, month & day parts and it could combine these individual parts in a single form. And, for creating a large dataset of Dates you need not retype again and again.

This useful function can change Dates dynamically by without effecting other values of existing dataset.

Basic Syntax

= Date (year, month, day)

As you can see from the syntax, Date function passes only three arguments which are year, month & day.
Example

Using this function, you can simply form a valid date by putting values of the individual year, month & day of your own.

Suppose, you want to create a date like 5/9/2020 or  May 9, 2020. You can either create it by manually input the values or by referencing the cells.

Working with manual input

Choose a blank cell and start typing “=Date” and then click on the suggested DATE function, like in the below image,


Then just type the values according to the format of the function and every argument will be separated by a comma. 

Look at the image given here,


Then just press enter of your keyboard. You will get a short date format result within a moment. Like this,


That’s set, isn’t it real quick and simple to manually work with Date function. You can also change this short date format to a long date format. Just go here,


Choose Long Date from the dropdown list,


And, the new result will look like this,


In next tutorial, we will show you, how you can work with Date function by referencing cells. Stay with tutorialabc for more easy and basic Excel tricks & lessons. (TBD) 


Thursday, April 23, 2020

Usage of SUMIF & SUMIFS in Excel

Today we will cover two basic yet very essential functions of MS Excel.
  1. SUMIF &
  2. SUMIFS

We believe, you already know the functionality of sum function. If you don’t know, please check out this SUM function tutorial.

SUMIF and SUMIFS have the same functionality likewise a SUM function but these two functions contain additional criteria to lookup the entire data table and based on those criteria it performs the sum operation.

Keep in mind, SUMIF function accepts only one criteria whereas SUMIFS function takes multiple criteria.




Introduction of SUMIF

SUMIF comes up with a comparison operator like greater than, less than or equal, etc, It supports both character and integer.

When there are a large number of data set their manual calculation is ineffective when you have to find out the range of sum based on condition. 

General syntax


=SUMIF (range, criteria, [sum_range])



Example & Working way,

Just think of a biscuit company inventory, where you need to find out how many kgs of flour ingredients is available there. So you must need a condition to apply in the sum that only flour ingredients need to sum up. Therefore, SUMIF function you can infuse a condition on SUM.

Now, for clear understanding, follow the below steps carefully.

1. Try to create or just copy & paste the below dataset on your excel worksheet as an example.

Product
Region
salesRep
Customer
Sales
bellen
South
zubbar
e
123
sun
East
zubbar
a
234
bellen
west
zubbar
g
345
sun
north
zubbar
w
456
zasa
East
zubbar
e
567
bellen
west
zubbar
e
678
zisa
north
zubbar
a
789
bellen
west
zubbar
g
344
sun
north
zubbar
w
1222
mss
East
akib
e
2100
zasa
west
akib
e
2978
bellen
East
akib
a
3856
sun
west
akib
g
4734

2. Then choose a blank cell and type “=SUMIF (A2:A14,"bellen", E1:E14)” to calculate the sum of bellen product sales and you will get a result of 4568 Sales based on the criteria.

Then, try to work with other products from the dataset to calculate the sum of the sales of those products. Hopefully, it will give you a clear concept, how actually SUMIF function work in MS-Excel.

Introduction of SUMIFS

SUMIFS function is almost the same as SUMIF. It has the power where you can set multiple conditions or on excel sense criteria.

General syntax


=SUMIFS (sum_range, range1, criteria1, [range2], [criteria2], ...)


Example & Working way,

1. Again, copy & paste the given dataset on your excel worksheet or prepare one by yourself.

Product
Region
salesRep
Customer
Sales
bellen
South
zubbar
e
123
sun
East
zubbar
a
234
bellen
west
zubbar
g
345
sun
north
zubbar
w
456
zasa
East
zubbar
e
567
bellen
west
zubbar
e
678
zisa
north
zubbar
a
789
bellen
west
zubbar
g
344
sun
north
zubbar
w
1222
mss
East
akib
e
2100
zasa
west
akib
e
2978
bellen
East
akib
a
3856
sun
west
akib
g
4734


2. Choose a blank cell where you want to show your sum result and start typing “=SUMIFS(E2:E14,A2:A14,"bellen",C2:C14,"zubbar")” to calculate the total sales of the product bellen which is sold under a salesRep named zubbar. You will get a result of 1490 Sales based on those criteria.

Hopefully, this tutorial makes some sense and got a clear understanding, how actually SUMIF & SUMIFS function work and also know the difference between them.

That’s set for today. If you have any inquiries or suggestions regarding this please write to us in the comment section. Stay with tutorialabc for more easy excel tutorials.