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.
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.
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”)
This will calculate number of suspension towers that are in Between A4 to A10. the result is shown below
Watch Video Explanation Step by step Here.
Our Latest Posts
- Comparison of Synchronous Condenser, Shunt Reactor, Capacitor Banks and STATCOM
- Engr. Hamid Ali
- L&T submits lowest bid of $268.2m for 380/132kV BSP and 380kV OHTL
- How to get Coursera courses for free
- How to obtain financial aid for Coursera courses
Categories:
- Control System (3)
- Research Work (2)
- Courses (5)
- Extra High Voltage EHV Substation Design (4)
- Fellowships (2)
- Information for Engineers (2)
- Interview Preparation (2)
- Jobs (10)
- Electrical Jobs (8)
- Jobs in KSA (9)
- L&T (1)
- MATLAB and Simulink (1)
- MCQs (12)
- Electrical MCQs (11)
- Electronics MCQs (9)
- Microsoft Excel (1)
- Past Papers (5)
- electrical (5)
- Saudi Electricity Company (2)
- Scholarships (4)
- Scholarships in Pakistan (1)
- Tendering (1)
- Transmission lines design (4)
- Uncategorized (18)
- Web Development (1)