Count items in a portion of column in Excel

Posted on by Engr. Hamid Ali

This post is about how you can count items in a portion of a column in excel dynamically. We will use countifs excel function combine with index function. For example Look at the Figure 1. We have different types of towers in column A and PI points in column B. Here we want to count number of specific Towers (suspensions in this case) in Column A that are between PI-1 to PI-2 (including these cells) in Column B.

countifs function in excel
Figure. 1 Excel portion

To do this first we have to find row numbers of cells that contain PI-1 and PI-2. For this we will use Match function. Then we will use countifs function combine with index function.

Using Match Function in Excel

To use match function in excel first we have to write in column D the expression, for which we are going to find the row number. Here we want to find the row number of PI-1 in Column B. To do this we use Match function. write =match(D10,B:B,0). Here D10 is the Item we want to find, B:B is the column where PI-1 is located, and 0 is for exact match.

Match function in Excel
Figure 2 Match function in Excel

Using Countifs function in Excel

Now Since we found the start and end row numbers of the portion. we proceed to countifs function. To use the countif function we have to provide range and criteria. To access dynamic range like we want to access range from A4 to A10 but we have just 4 and 10 as shown in figure. for this we use index(A:A, Row number) to index(A:A, Row number) and then enter criteria as shown.
=COUNTIFS(INDEX(A:A,E10):INDEX(A:A,E11),”suspension”)

count in a portion of a column excel
Figure 3 CountIFS Excel

This will calculate number of suspension towers that are in Between A4 to A10. the result is shown below

countifs function in excel
Figure 4 Result of formula

Watch Video Explanation Step by step Here.

Our Latest Posts

Categories: