Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Formula requested

Copper Contributor

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

20SUP3
20SUP3
20SUP2
20SUP5
21STD3
21SUP3
22EXEC1
22STD1
23JSUI3
23SUP2
23STD3
24SUP7
24SUP2
25STD2
27EXEC1
27SUP8
27STD3
28SUP3
28STD2
28STD1
29EXEC1
29STD5
30STD2
30EXEC1
30STD3
30JSUI5
30JSUI5
30STD5
30STD1

 

 1234567123456712345671234567
 STDSTDSTDSTDSTDSTDSTDSUPSUPSUPSUPSUPSUPSUPEXEEXEEXEEXEEXEEXEEXEJSUIJSUIJSUIJSUIJSUIJSUIJSUI
20        12 1                
21                            
22                            
23                            
24                            
25                            
26                            
27                            
28                            
29                            
30                            
                             
4 Replies

@George_Gyftopoulos 

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.

@George_Gyftopoulos 

How a bout a regular pivot table? See attached.

 

@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