Excel Formula to find unique and duplicate values in various ranges, and then count
I need some help writing a formula in excel to count how many days I had a staff member present at each particular site.
My actual spreadsheet is a bit more complex, so I have drafted a simple version of what I am looking to achieve. I have a total of 10 staff and 5 sites, but for simplicity, I have included 3 staff and 3 sites, as once I have the formula, I can hopefully just expand the ranges to include more staff and sites as and when it is needed in my main spreadsheet.
As you can see, the total number of days a staff member was present at Site A was 7 (with the days being 1, 2, 5, 9, 12, 13, & 15). However, some of these days had more than 1 staff member present, but these days should only be counted once in the bottom table "Total number of days staff were present", as the requirement is to count how many days 1 or more member of staff were present on site.
I am therefore looking for a formula which can go in B23 which will compare multiple ranges (in this scenario for "Site A" it would be "B3:S3, B10:S10 & B17:S17", and then find and COUNT the unique numbers/dates in these ranges, and then also find and COUNT the duplicate numbers/dates within the ranges as well, and then ADD these two Counts together.
Thanks in advance.
After much hunting around I managed to find a simple formula which is linked to specific ranges, allowing me to add more staff and more sites as required:
B23(Site A) =COUNT(UNIQUE(TOCOL(VSTACK(B3:S3,B10:S10,B17:S17),1)))
B24(Site B) =COUNT(UNIQUE(TOCOL(VSTACK(B4:S4,B11:S11,B18:S18),1)))
B25(Site C) =COUNT(UNIQUE(TOCOL(VSTACK(B5:S5,B12:S12,B19:S19),1)))
- Excel formula to convert number to both feet and inches
- How can I perform a reverse string search in Excel without using VBA?
- How to condense one table into a smaller one, stacking multiple cells, with a formula?
- Table to summarise data into a spreadsheet
- Match data based on id between sheets of one googlesheet &extract alphanumeric values in separate column
- Need formula to help cross-check values and conditions in different columns
- Search for groups of cells that contain values, calculate the difference, place result in adjacent column
- convert column range into single column one column below the next column
- Excel to dynamically generate SQL script using formula
- How to loop in excel without VBA or macros?
- EXCEL randbetween- text representation - clarification
- Generate a truth table in excel
- Sortby function combined with Unique and filter functions: how to use it correclty?
- Count of Unique Values based on who it's assigned to?
- VBA or Excel? Lookup reference from one column into and return least occurring value from another
- IF function checking 2 different columns of data
- using XLOOKUP to find matching names over multiple tabs and supplying data from specific cells as a result
- Excel Formula to find unique and duplicate values in various ranges, and then count
- In Excel, how to extract and convert unsorted comma-seperated data from one column into multiple columns?
- Creating Dynamic Nested List with Excel Formula from Pre-Made Tables (on 3 levels)
- How do i use Excel Random list generation without duplicate
- Formula to search for specific Chars within a cell
- How to MATCH text with a wildcard in the target matrix
- What is the equivalent of the "Days" function or number of days between 2 dates in excel 2007?
- Need help aligning a table to a column based on similar values in one table field
- Index rows and columns unexpected results
- Automatic numbering in Excel with hierarchy
- How to do a row-wise sum in an array formula in Excel?
- Transform datatable from column-based to row-based
- Combine most recent 5 records into one cell in Excel