Category: Formulas

SUMPRODUCT can COUNT

Formulas

Hi there,

 

In this post, we will see how to use SUMPRODUCT to replace COUNT, COUNTA and COUNTIF functions.

 

COUNT:

This function is used to count the number of values in a range of cells.

eg: we have a range A1:A10 which contains values with A5 as blank and A8 as alpha numeric, then COUNT will display the result as 8 as there is only 8 cells which contains values or numerics.

Full story »

DATE with RANDBETWEEN

Formulas

Hi there,

In this post, we will see how to use RANDBETWEEN for randomly appearing dates in a range of cells. This is very simple but logical. Let me share it with you now, to do this, first select the range where these dates to be displayed and use the below function to display 15 dates randomly.

Download Working File.xls


=RANDBETWEEN("01-01-2015","01-15-2015")

or

=RANDBETWEEN(42005,42019)

 

Full story »

SUM based on Colour (No Cast & No Religion)

Formulas, CF

Hi there,

 

In this post, I will be sharing three slides on how to SUM values based on colour, without considering its cast and its religion ;)

Let me know what you have understood after reviewing these three slides. And, if you still requires explanation on this post, please post your comment as "Explain Please". 

 

Here we go:

Full story »

Sum Values Based on Two Conditions

Formulas

Hi there,

 

Recently, I have completed one batch of MASTER@HOME program and in the mean time I gave a question to my students related to the post title. The question is Sum the values in column C based on the values >30 and the values <65? I selected this question randomly based on the data availability while giving basic training . This question should actually be listed in advanced formulas because this can be achieved by using SUMIFS function.

If you know how to sum based on two conditions, please do post your formula in the below comments section (take random values to sum). If you want to learn how to do this, please step with me.

Full story »

4 Ways to Colour Alternative Rows (with no time)

Formulas, CF, Tables, Fill Handle Tricks

Hi there,

 

I am sure, many of you are familiar about Table feature in Excel. Some time, while working on the data we feel like filling colour to an alternative rows to showcase them nicely to the management. We can achieve this using four different methods. I bet you will atleast like one of them. These tips are much easier and can save lots of time rather colouring it manually. Let us go ahead now:

Colour alternative rows using four options using excel

There are four different ways to colour alternative rows in Excel:

1. Using Conditional Formatting (more here)

2. Using Table 

3. Using Auto Fill Option

4. Using Banded Rows.


Let me explain each of them:

Full story »

Who Said VLOOKUP extracts only from LEFT to RIGHT

Formulas

Hi there,

 

Here is an another great post, on VLOOKUP. Most of you all known VLOOKUP extracts data matching from left column to give the output of right column. But, in this post you will be learning how to extract values from any column using VLOOKUP.

I am so confident that this will be one of my great posts, that I have posted in this blog. If yes, leave your comment in the below comment section.

 

Let's proceed to achieve this goal. Before that, Download Working File.xlsx and click on FULL STORY here,

Full story »

Contact / Help. ©2019 by Abhilash VK. Design & icons by N.Design Studio. Skin by Tender Feelings / EvoFactory.