In this lesson you will teach yourself how to count the number of cells with specific content in a range in clever way. You need only one formula to do this!
To count a number of cells with a text in a specific range you just need to use sumproduct function. You can build formula like this:
=SUMPRODUCT(--ISTEXT(RANGE))
You can count a number of cells with a value as well with similar formula:
=SUMPRODUCT(--ISNUMBER(RANGE))
There are others functions you can use with this kind of formulas:
- =SUMPRODUCT(--ISBLANK(RANGE))
- =SUMPRODUCT(--ISERR(RANGE))
- =SUMPRODUCT(--ISERROR(RANGE))
- =SUMPRODUCT(--ISEVEN(NUMBER))
- =SUMPRODUCT(--ISFORMULA(RANGE))
- =SUMPRODUCT(--ISLOGICAL(RANGE))
- =SUMPRODUCT(--ISNA(RANGE))
- =SUMPRODUCT(--ISNONTEXT(RANGE))
- =SUMPRODUCT(--ISODD(NUMBER))
- =SUMPRODUCT(--ISOWEEKNUM(DATE))
- =SUMPRODUCT(--ISPMT(RATE,PER,NPER,PV))
- =SUMPRODUCT(--ISREF(RANGE))
Isn't an Excel wonderful that makes your life so much easier?