Formula to count multiple various inputs and create a summary

  • Thread starter Thread starter Kelstar
  • Start date Start date
K

Kelstar

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?
 
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.
 
JLatham said:
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 said:
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?
Click to expand...
Click to expand...


Worked perfectly, thank you for your help.
 
Back
Top