How to make status summary report using IF and SUMIF excel function for text data

This is one of the sample techniques to make summary report. Although most of the project and maintenance software can produce summary report, we need additional customized reports to be produced as and when required. The result of this technique is similar to using IF function and pivot table wizard. The difference is that it is not reliable to make customized format or presentable format although the final data is the same.

This method is reliable and customizable as required. It can be prepared in a short time if you know how to use IF and SUMIF function very well. The latest status report can be viewed immediately once the latest information is imported or updated.
Suppose you have a spread sheet containing text data which may be exported from other planning tools or may be in the same spread sheet. For instance, a spread sheet containing the status and the detail information of pipe production and delivery is available as follows;

To produce the summary report from the above table, follow the below steps.
1. Add dummy columns in the same spread sheets to track reference columns in that sheet. Give the field names. Field names, “Count Drawing”, “Count fabricated”, “Count Paint Required”, “Count Send to Paint yard” and “Count Deliver to site” are given in this post.

2.Use IF function in each cell to return the status as a value of 1 or 0. If the reference cell is number format, use the formula “=IF (reference cell>0, 1, 0). If the reference cell is text, use “=IF(reference cell=”text” in the reference cell, 1, 0). Formula "=IF(D3>1,1,0) is used in the cell (number format) for field name "Count Drawing" and Drawing No. 1101 row. Formula "=IF(H3="YES",1,0) is used in the text data cell for field name "Count Paint Required" and Drawing No.1101 row. See figure below.

3. Create project level summary table grouped as project codes to track by project. Create another table grouped as material types to report the status by material type. Use SUMIF function to sum up the data in dummy columns. Finally you will get the report as below.

Based on the above summary report, overall status are the same and each status is different due to different grouping. You only need to use SUMIF function when you analyze value (numerical) data. I hope you could follow above steps.

Get the downloadable file to learn more>>