exceltoxl.com

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.

...

COUNTA:

This function is used to COUNT values, alphabets and special characters. 

• Values which means numbers eg: 0,1,2,3, -1,-2, -3,...
• Alphabets includes "A" to "Z" or "a" to "z"
• Special characters includes click here

SUMPRODUCT:

This function is used for multipurpose and it is an awesome advanced function, however, here we will see how to use this function to replace with count and countif functions in excel.

Say, we have a range of data with names and to count the particular name in a range of cells we can even use SUMPRODUCT. The formula looks like this:

Similarly, we can even count the dates which stands our condition like >, <, =, <>, >= or <=. Use cell reference like above.

DID YOU USE SUMPRODUCT:

Please teach us how you use sumproduct in your work using below comments section.

If you like this post, please do post it on your social networking sites by clicking on the icons placed around the blog.

If you want to receive such tips, please do Register here for free.

SOME IMPORTANT LINKS:

Usage of Excel on day to day

Excel Tips & Tricks

Pic Random Words using RANDBETWEEN

Getting Decimals using RANDBETWEEN

Crack Formula Errors

Formulas Crash Course

Thanks,