Column of text needs "summed" based on assigned value
Gillian
On one sheet, I have a table with an employee name in column A and their associated rank in column B. On a separate sheet, I have a blank master schedule with all possible shifts in column B and days of the week in row 1. I would like to be able to type the employee's name in the cell associated with the shift they will be working. After filling in the schedule, I would like excel to "add" up the ranks to get a sum total for each day. If there are empty cells, they would be given a value of "0". I made it work using the following formula:
=SUM(COUNTIF(C2:C19,{"A","B","C"})*{1,2,3})
where A, B, and C represent the employees' names and 1, 2, and 3 represent their associated ranks, but admittedly that was before I introduced the possibility of blank cells. The problem there is that I have to freetype it all, and if a rank changes, or an employee changes, I have to edit everything individually. I'm really hoping there's a way to reference my table and I'm just not getting it?
Any help would be greatly appreciated!!
teylyn
Create dynamic ranges names for the employee names and the ranks, for example
Names =Sheet1!$A$2:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A))
Ranks =OFFSET(Names,0,1)
On the other sheet, use the formula
=SUM(COUNTIF(C2:C19,Names)*Ranks)
This is an array formula and must be confirmed with Ctrl-Shift-Enter
You can now add new names to the employee list...
... and use that name in the shift roster. The formula will show the correct result.
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
编辑于
相关文章
Related 相关文章
- 1
在Kendo Grid MVC中按外键排序Column Text not value
- 2
Adding column with default value to large table
- 3
SQL column value null, search filter mistaken
- 4
How to update a column with another tables columns value?
- 5
Add button + text in Infragistics ultragrid column
- 6
D3 bar chart needs to add arrow and text in particular bar
- 7
MySQL group chunks by column value, sorted by other column
- 8
Add text to a value in a column
- 9
Sorting a numeric column in a text file with python
- 10
Change values in row based on a column value and replacing specified column range
- 11
PostgreSQL - column value changed - select query optimization
- 12
Add a column, with a default value, to an existing table in oracle
- 13
mysql query based on value of previous row
- 14
PostgreSQL add id column that increments based on data
- 15
Column Visibility based on Multiple multi value parameters SSRS
- 16
How to assign a value to a particular sequence in a column in r?
- 17
Swift: Optional Text In Optional Value
- 18
Get the Column with the Maximum Same Value
- 19
Illegal attempt to use Text/Byte host variable - Inserting into TEXT column
- 20
By row, replace values equal to value in specified column
- 21
Ranking - Select corresponding column values to a MAX(column) value
- 22
How to get records if a column has value A, if not then B?
- 23
How to ORDER BY count of rows with specific column value
- 24
join 2 mysql select based on text field
- 25
replace velues in column matching based on 2 columns
- 26
在Kendo Grid MVC中按外键排序Column Text not value
- 27
Excel - Live filter of list based on another column?
- 28
Split CSV files based on part of a column
- 29
SQL WHERE IS NULL 或 column = 'value'
我来说两句