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 m...
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?
