Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula to count multiple various inputs and create a summary
Hi,
I've got a spreadsheet which has multiple rows and collums of data that I log issues in and I need to find a way to produce a summary. So, each row contains, status (open, closed etc) and a severity (critical, high, etc) I need to create a summary page which shows numbers of issues by status and severity - e.g. Critical High Medium Low New 5 1 Open Fixed 3 2 Closed Reopen TOTAL 5 3 3 0 Is it possible to get the summary to populate itself? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula to count multiple various inputs and create a summary
Yes, it's possible, and not too difficult to implement.
Assumptions: your list of individual entries is on 'Sheet1' with the stage (New, Open, Fixed, etc) in Column B, beginning at row 2, and the status (Critical, High, etc) in column C again beginning at row 2. The Summary table that you've shown here is on another sheet and begins at A1 (the blank, upper left cell) with "Critical" in B1, and "New" in A2. In cell B2 enter a formula similar to this: =SUMPRODUCT(--(Sheet1!$B$2:$B$12=$A2),--(Sheet1!$C$2:$C$12=B$1)) Be sure to use the $ symbols where I've shown. Change the references to $B$2:$B$12 and $C$2:$C$12 to include at least all rows with entries on Sheet1. It's ok if you use a big number to help deal with added entries later, like =SUMPRODUCT(--(Sheet1!$B$2:$B$10000=$A2),--(Sheet1!$C$2:$C$10000=B$1)) just make sure that the row numbers are the same for both B2:B# and C2:C#. With that formula set up in B2, you can now fill it over to the right to column E (under "Low") and then fill all 4 down to row 6 (Reopen) and set up your SUM() formulas on row 7. Done! If you don't want to see the zero entries, you can either: Clear the check box next to "Show Zero Entries" under Tools -- Options on the [View] tab, or Use conditional formatting in the cells with the SUMPRODUCT() formulas in them to change the text color to the same as the cell color to make them invisible. "Kelstar" wrote: Hi, I've got a spreadsheet which has multiple rows and collums of data that I log issues in and I need to find a way to produce a summary. So, each row contains, status (open, closed etc) and a severity (critical, high, etc) I need to create a summary page which shows numbers of issues by status and severity - e.g. Critical High Medium Low New 5 1 Open Fixed 3 2 Closed Reopen TOTAL 5 3 3 0 Is it possible to get the summary to populate itself? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula to count multiple various inputs and create a summary
"JLatham" wrote: Yes, it's possible, and not too difficult to implement. Assumptions: your list of individual entries is on 'Sheet1' with the stage (New, Open, Fixed, etc) in Column B, beginning at row 2, and the status (Critical, High, etc) in column C again beginning at row 2. The Summary table that you've shown here is on another sheet and begins at A1 (the blank, upper left cell) with "Critical" in B1, and "New" in A2. In cell B2 enter a formula similar to this: =SUMPRODUCT(--(Sheet1!$B$2:$B$12=$A2),--(Sheet1!$C$2:$C$12=B$1)) Be sure to use the $ symbols where I've shown. Change the references to $B$2:$B$12 and $C$2:$C$12 to include at least all rows with entries on Sheet1. It's ok if you use a big number to help deal with added entries later, like =SUMPRODUCT(--(Sheet1!$B$2:$B$10000=$A2),--(Sheet1!$C$2:$C$10000=B$1)) just make sure that the row numbers are the same for both B2:B# and C2:C#. With that formula set up in B2, you can now fill it over to the right to column E (under "Low") and then fill all 4 down to row 6 (Reopen) and set up your SUM() formulas on row 7. Done! If you don't want to see the zero entries, you can either: Clear the check box next to "Show Zero Entries" under Tools -- Options on the [View] tab, or Use conditional formatting in the cells with the SUMPRODUCT() formulas in them to change the text color to the same as the cell color to make them invisible. "Kelstar" wrote: Hi, I've got a spreadsheet which has multiple rows and collums of data that I log issues in and I need to find a way to produce a summary. So, each row contains, status (open, closed etc) and a severity (critical, high, etc) I need to create a summary page which shows numbers of issues by status and severity - e.g. Critical High Medium Low New 5 1 Open Fixed 3 2 Closed Reopen TOTAL 5 3 3 0 Is it possible to get the summary to populate itself? Worked perfectly, thank you for your help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help using data from multiple worksheets to create a Summary | Excel Discussion (Misc queries) | |||
Create a summary list using same fields from multiple sheets | Excel Discussion (Misc queries) | |||
Can I have a formula with inputs from multiple pages? | Excel Worksheet Functions | |||
Copying Cells From Multiple Worksheets to Create Summary Sheet | Excel Discussion (Misc queries) | |||
How do I create a formula that inputs the date when a cell equals. | Excel Discussion (Misc queries) |