Forum Discussion
Formula requested
Hello, I have a table with data I want to analyze in the second table. How can I automate this in the second table? I have an example for the first row. Thank you in advance. This would be of great help.
InRmsDays
| 20 | SUP | 3 |
| 20 | SUP | 3 |
| 20 | SUP | 2 |
| 20 | SUP | 5 |
| 21 | STD | 3 |
| 21 | SUP | 3 |
| 22 | EXEC | 1 |
| 22 | STD | 1 |
| 23 | JSUI | 3 |
| 23 | SUP | 2 |
| 23 | STD | 3 |
| 24 | SUP | 7 |
| 24 | SUP | 2 |
| 25 | STD | 2 |
| 27 | EXEC | 1 |
| 27 | SUP | 8 |
| 27 | STD | 3 |
| 28 | SUP | 3 |
| 28 | STD | 2 |
| 28 | STD | 1 |
| 29 | EXEC | 1 |
| 29 | STD | 5 |
| 30 | STD | 2 |
| 30 | EXEC | 1 |
| 30 | STD | 3 |
| 30 | JSUI | 5 |
| 30 | JSUI | 5 |
| 30 | STD | 5 |
| 30 | STD | 1 |
| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | |
| STD | STD | STD | STD | STD | STD | STD | SUP | SUP | SUP | SUP | SUP | SUP | SUP | EXE | EXE | EXE | EXE | EXE | EXE | EXE | JSUI | JSUI | JSUI | JSUI | JSUI | JSUI | JSUI | |
| 20 | 1 | 2 | 1 | |||||||||||||||||||||||||
| 21 | ||||||||||||||||||||||||||||
| 22 | ||||||||||||||||||||||||||||
| 23 | ||||||||||||||||||||||||||||
| 24 | ||||||||||||||||||||||||||||
| 25 | ||||||||||||||||||||||||||||
| 26 | ||||||||||||||||||||||||||||
| 27 | ||||||||||||||||||||||||||||
| 28 | ||||||||||||||||||||||||||||
| 29 | ||||||||||||||||||||||||||||
| 30 | ||||||||||||||||||||||||||||
Your post is rather short on information, so I'm going to make a number of assumptions:
- I will assume that the "tables" that you mention are ranges, not Excel tables. The two are referenced using different syntaxes.
- You do not mention whether the two ranges ("tables") are on the same worksheet, or on different worksheets, or which columns contain your three columns of your first range ("table") of data. I will assume that they are on separate worksheets, the first containing data in columns A, B, and C, countable values starting in row 2, and the worksheet is named Detail Data. I will also assume that your summary "table" has its uppermost-leftmost formula in cell C4.
Column A data is labelled InRms, but columns B and C have no names; I will call their data Lumpy and Dumpy, respectively. - You do not mention how many rows of data there are on Detail Data. I will assume up to 500 rows. (It's easy to increase that number, particularly if you do so at the start.)
- I will further assume that the values on Detail Data are "clean", in that all InRms and Dumpy values (which appear to be numbers) are recognized by Excel as numbers (or conversely, that they are all text values), and that none of the Lumpy values include leading or trailing spaces. Similarly for the second worksheet values (column headers and row headers).
- You do not mention which version of Excel you are using. I am going to assume that your version is recent enough to support the COUNTIFS function.
All formulas within the summary sheet (containing the counts) need to refer to the same rows and the correct columns, even when those formulas are copied. So it's important for them to refer to the Detail Data values using absolute references, that is, prefixing the column letters and row numbers with dollar signs; for example: $A$2:$A$501
The references in those formulas to values in the column headers and row headers will use mixed references (absolute column references and relative row references for the row headers; conversely for the column headers).
Then the formula in cell C4 can be:=COUNTIFS('Detail Data'!$A$2:$A$501, $B4, 'Detail Data'!$B$2:$B$501, C$3, 'Detail Data'!$C$2:$C$501, C$2)This formula can be copied to all the other cells in the same row, and those formulas copied down through row 14 (the row with an InRms value of 30).
If you don't want to see zeroes in each of the formula cells, you can use formatting to reduce the visual clutter, for example, the custom format 0;;"-";@ or you can use conditional formatting to make the non-zero numbers bold, or use a larger font, or a different background color, etc.
Note that this design will ignore any "mistakes" or "new values" (e.g., an InRms value of 32 or a Lumpy value of SIP or that includes a leading/trailing space).See the attached workbook.
SnowMan55 Thank you very much for your time and answer. I appreciate it. Sorry for not providing adequate information. The formula is working. I can work on this.
Have a great day.
George
- Thank you very much for your reply. I tried to create one myself but it did not work. I will check this one.
Have a good day.
George
