Pages

  • Home
  • About
  • Download
  • Tutorials

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.

5 comments:

  1. Through this, it will be a lot easier for me to use excel. Thanks!

    ReplyDelete
  2. Fabricated reports really need excel as its platform. Thanks for demonstrating it properly.

    ReplyDelete
  3. I've been looking about it because I already forgot how to do the if function in Excel. Anyway, using Excel spreadsheet in doing reports makes it more organize, convenient and presentable.

    ReplyDelete
  4. Although paragraph structure challenges writers, it is essential not only to help organize the content logically but also to keep the reader interested to the end of the document. summarizer online

    ReplyDelete
  5. Status is a hierarchy, shaped like a pyramid and represents your standing of respect and esteem in the eyes of those people you see everyday; at its extreme it is referred to as a pecking order. Status

    ReplyDelete

Related Posts Plugin for WordPress, Blogger...