How to count values in one column based on another in cells
Suppose I have a table tracking an employees leave at a business. It has the reason for leave, leave type, total days and day range. For this, only the type and total days matter.
I want to be able to see a brief summary, in numbers, of how many of each of the leave types this employee has used. I've attached a sample of the data below as a photo and raw table. For example, this employee has taken 7 days as LSL, so in the coloured table on the right I'd like a way for Excel to count this for me and display 7 in the box.
Image of the table in Excel - also shown below for convenience
| Leave Type | Leave Days |
|---|---|
| A/L | 3 |
| A/L | 1 |
| A/L | 1 |
| A/L | 2 |
| A/L | 3 |
| A/L | 3 |
| DIL | 1 |
| A/L | 2 |
| A/L | 9 |
| A/L | 1 |
| A/L | 3 |
| DIL | 1 |
| LSL | 4 |
| DIL | 1 |
| DIL | 1 |
| A/L | 2 |
| SICK | 4 |
| A/L | 1 |
| DIL | 5 |
| DIL | 1 |
| LSL | 3 |
I have tried using some VLOOKUP or HLOOKUP but can never seem to get it right!
This is pretty textbook example of sumif.
Put this in your worksheet in H3 and drag down =Sumif(B:B,G3,C:C)
If you wanted to get something more dynamic (and fancy), you could drop this formula in cell G3 and it would dynamically update as you had new items in column b.
=LET(zData,DROP(UNIQUE(TOCOL(B:B,1)),1),HSTACK(zData,SUMIF(B:B,zData,C:C)))
- Largest sum of consecutive values in row, but is it still consecutive in the cell of the last column?
- Dynamically Inserting a row and iterating over it in VBA?
- VBA class member function and class definition problem continues
- How can I perform COALESCE in power query?
- VBA class member function and class definition problem
- How can I set OptionButton font using VBA For Loop?
- VBA remove string custom salutation name from list table excel
- How to import many dataframes from R into a one Excel file in a simple way?
- How to add a missing column to the Transform Sample File
- Pull data from multiple workbooks based on a dynamic list
- Dependent dropdown list in Excel ,Data Validation and VBA
- Excel Formula: Filter Group based on Smallest Values
- Find column name of active cell within ListObject in Excel
- Strange characters found in XML file and PowerShell output after exporting from Excel: ​
- MID function inside COUNTIFS
- Finding Max of 3 Inputs VBA
- How to align duplicates on the same rows in Excel
- How to use .HTMLBody to hyperlink a cell value?
- Adding cell values, specific to each page, to each header
- Excel VBA macro for URL redirect detecting returns 200 instead of 301 sometimes
- Count with specific values in columns
- Convert IEEE Float hex string to an Excel VBA Single (float)
- Openpyxl or Pandas, which is better at reading data from a excel file and returning corresponding values
- Excel Function: Using a specific criteria to form an array to corresponding values
- How to pass metadata to Google Drive API upload with VBA
- IsError not capturing error returned by expression
- Excel VBA multi select drop down list
- Imported Excel file with dates, came out as numbers, trying to return to date format
- Creating Conditional Column based on another Table's Column
- Convert Range to a table