Excel Office

Excel How Tos, Tutorials, Tips & Tricks, Shortcuts

Tag: COUNTA function

COUNTA function: Description, Usage, Syntax, Examples and Explanation

What is COUNTA function in Excel? The COUNTA function is one of the Statistical functions in Microsoft Excel which counts the number of cells that are not empty in a range. Syntax of COUNTA function COUNTA(value1, [value2], …) The COUNTA function syntax has the following arguments: value1    Required. The first argument representing the values that you want to count. value2, …    Optional. Additional arguments representing the values that you want to…

Basic outline numbering in Excel

This tutorial shows how to create 1st and 2nd level outline formatting using excel formulas. To generate basic outline numbering you can use COUNTA, MID, FIND, IF, and LEN functions. Note: The formula used will only handle a 2-level outline. Explanation In the example shown, the formula in D5 is: =COUNTA($B$5:B5)&”.”&IF(B5<>””,1, MID(D4,FIND(“.”,D4)+1,LEN(D4))+1) How this formula works At the core, this formula builds…

Check if multiple cells have same value in Excel

To confirm that a range of cells all have the same value, you can use a formula based on the COUNTIF function. Formula =COUNTIF(range,”<>value”)=0 Explanation In the example shown, the formula in C9 is: =COUNTIF(C5:C8,”<>ok”)=0 Note: this formula is not case-sensitive, you can find a case-sensitive formula here. How this formula works This formula relies on the standard behavior of the…

Check if multiple cells have same value with case sensitive in Excel

To verify that multiple cells have the same value with a case-sensitive formula, you can use a simple array formula based on the EXACT and AND functions. See example below: Formula {=AND(EXACT(range,value))} Explanation In the example shown, the formula in G5 is: =AND(EXACT(B5:F5,B5)) This is an array formula and must be entered with control + shift + enter How this…

Check multiple cells have same value in Excel

To confirm that a range of cells all have the same value, you can use a formula based on the COUNTIF function. See illustration below: Formula =COUNTIF(range,”<>value”)=0 Explanation  In the example shown, the formula in C9 is: =COUNTIF(C5:C8,”<>ok”)=0 Note: this formula is not case-sensitive, you can find a case-sensitive formula here. How this formula works This formula relies on the standard…

How to get last row in mixed data with no blanks in Excel

To get the last relative position (i.e. last row, last column) for mixed data that contains no empty cells, you can use the COUNTA function. See example below: Formula =COUNTA(range) Explanation In the example shown, the formula in D5 is: =COUNTA(B4:B100) Last *relative* position When constructing more advanced formulas, it’s often necessary to figure out the last location of data…

How to create dynamic named range with OFFSET in Excel

One way to create a dynamic named range with a formula is to use the OFFSET function together with the COUNTA function. Dynamic ranges are also known as expanding ranges – they automatically expand and contract  to accommodate new or deleted data. Note: OFFSET is a volatile function, which means it recalculates with every change to a worksheet. With a modern machine…

How to create dynamic named range with INDEX in Excel

This tutorials show examples one and two dynamic named ranges created. The first is created with the INDEX function together with the COUNTA function. Dynamic named ranges automatically expand and contract when data is added or removed. Formula =$A$1:INDEX($A:$A,lastrow) Explanation This page shows an example of a dynamic named range created with the INDEX function together with the COUNTA function. Dynamic named ranges automatically expand…

How to calculate project complete percentage in Excel

To calculate the percentage complete for a project with a list of tasks, you can use a simple formula based on the COUNTA function. Formula =COUNTA(range1)/COUNTA(range2) Explanation In the example shown, the formula in F6 is: =COUNTA(C5:C11)/COUNTA(B5:B11) How this formula works At the core, this formula simply divides tasks complete by the total task count: =complete/total which is then formatted…

How to check if cell contains all of many things in Excel

If you want to test a cell to see if it contains all items in a list, you can do so with a formula that uses the SEARCH function, with help from the ISNUMBER, SUMPRODUCT, and COUNTA functions. Case study: Let’s say you have a list of text strings in the range B5:B8, and you want to find out if these…

Running count group by n size in Excel

This tutorial shows how to Running count group by n size in Excel using the example below; Formula =CEILING(COUNTA(expanding_range)/size,1) > Explanation To creating a running count of groups of a variable size, you can use the COUNTA and CEILING function. In the example shown, C5 contains this formula: =CEILING(COUNTA($B$5:B5)/size,1) where “size” is the named range F4. How this formula works The core…

Count cells not equal to many things in Excel

This tutorial shows how to Count cells not equal to many things in Excel using the example below; Formula =SUMPRODUCT(–(ISNA(MATCH(data,exclude,0)))) Explanation To count cells not equal to any of many things, you can use a formula based on the MATCH, ISNA, and SUMPRODUCT functions. In the example shown, the formula in cell F5 is: =SUMPRODUCT(–(ISNA(MATCH(data,exclude,0)))) where “data” is the named range B5:B16…

Count cells that are not blank in Excel

This tutorial shows how to Count cells that are not blank in Excel using the example below; Formula =COUNTA(range) Explanation To count the number of cells that are not blank in a range, you can use the COUNTA function. In the example shown, E5 contains this formula: =COUNTA(B4:B8) How this formula works COUNTA is fully automatic. When given a range of…

Count cells that are blank in Excel

This tutorial shows how to Count cells that are blank in Excel using the example below; Formula =COUNTBLANK(range) Explanation To count the number of cells that are blank, you can use the COUNTBLANK function. In the example, the active cell contains this formula: =COUNTBLANK(B4:B8) How this formula works COUNTIF counts the number of cells in the range that don’t contain any value…

Count sold and remaining in Excel

This tutorial shows how to Count sold and remaining in Excel using the example below; Formula =COUNTA(range1)-COUNTA(range2) Explanation If you have a list of items, and need to count how many you have total, how many are sold, how remain, etc., you can use the COUNTA function. This can be useful if you are selling tickets, seats, entries, or anything…

Summary count with percentage breakdown in Excel

This tutorial shows Summary count with percentage breakdown in Excel using the example below; Formula =COUNTIF(range,criteria)/COUNTA(range) Explanation To generate a count with a percentage breakdown, you can use the COUNTIF or COUNTIFS function, together with COUNTA. In the example shown the formula in H4 is: =COUNTIF(category,F4)/COUNTA(category) How this formula works COUNTIF is set up to count cells in the named…