Forum Discussion
EXCEL FORMULA ERROR
Hi All,
I'm looking to create a formula that searches for codes across various departments and collates them together on a top level sheet. This will be an easier way of showing the data to snr management. (I'll then take this data and put into conditional formatting to show capacity level)
There are 5 departments (totally 50 staff) and all names are always located in column A, the sheet contains each day of the year and I want it to look for the code (BNXXX) (the x's will be different numbers) split between each month. I can do this basically using countif, then selecting range etc, but sometimes we have staff leave/join, which leads me to move the names around.
Ideally I'd like the formula to do the following:
On Sheet1 search for (jane_doe) and count up between this range (january) and tally up how many times *bn* is used. Allow formula to work if jane_doe moves from row 5 to 10.
I have the following, but excel is throwing up errors
=COUNTIFS(Sheet1_2024!A8:A27,"jane doe",Sheet1_2024!B8:AF9,"*bn*")
- I think the problem is the ranges are different sizes in your countifs:
=COUNTIFS(Sheet1_2024!A8:A27,"jane doe",Sheet1_2024!B8:AF9,"*bn*")
the name range is 20 cells (in column A) while the *bn* is 62 cells I believe and not a 1-d array (single row or column) but a 2-d array of 2 rows from column B to column AF
It is possible to do this on different size areas but there are limits and we need to know how it all maps. In this case I don't know what you need. Can you share an example sheet (no private info please).- Hi mtarler
The sheet range to check for BN is each day of the month (1,2,3,4 etc) and it's 2 rows deep to allow for a morning booking and afternoon booking.
The range to search for the name it for the the staff name and job role.
In terms of sharing an example sheet, let me explain below:
For example:
COLUMN A - STAFF NAMES (set out across 2 rows Name, Title)
A8 - Jane Doe
A9 - Manager
Month is set up to show from Column B along to AF, with dates and days shown.- in simplest terms would this work:
=(Sheet1_2024!A8="jane doe")*COUNTIF(Sheet1_2024!B8:AF9,"*bn*")
if so and you want more 'functionality' or feature or what not we can work on that. I just want to know if I'm 'seeing' your sheet structure right.
Hi SmitLaur930,
you can try this:Formula:
=COUNTIFS(INDIRECT(Sheet1_2024!A8&":"&Sheet1_2024!A27),"jane doe",Sheet1_2024!B8:AF9,"*bn*")- The INDIRECT function converts a text string into a cell reference. In this case, we are using it to create a dynamic range reference that includes all of the rows in column A from row 8 to row 27.
- The COUNTIFS function counts the number of cells in a range that meet multiple criteria. In this case, we are using it to count the number of cells in the dynamic range reference that contain the name "jane doe" and the code "bn".
For example:
Sheet1_2024 Name | January | February | March | ... ----- | -------- | --------- | -------- | ... Jane Doe | BN123 | BN456 | BN789 | ...
Formula:
=COUNTIFS(INDIRECT(Sheet1_2024!A8&":"&Sheet1_2024!A27),"jane doe",Sheet1_2024!B8:AF9,"*bn*")The result is: 3
Please click Mark as Best Response & Like if my post helped you to solve your issue.
This will help others to find the correct solution easily. It also closes the item.If the post was useful in other ways, please consider giving it Like.
Kindest regards,
Leon Pavesic
(LinkedIn)Thanks for replying - what you've said makes sense, but when I input, it still comes up with REF! error.
Is there any way to drill down where the error in the formula may be?
- The INDIRECT function converts a text string into a cell reference. In this case, we are using it to create a dynamic range reference that includes all of the rows in column A from row 8 to row 27.
