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...
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.
=(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.
The *COUNTIF(Sheet1_2024!B8:AF9,"*bn*") works, but the checking for the row that includes jane doe's name does not. As soon as I add this in, the results come back as 0.
I could complete the task manually, and hope noone moves the names, but surely there must be a way? Would XLookup help with this?
I could complete the task manually, and hope noone moves the names, but surely there must be a way? Would XLookup help with this?
- note that (Sheet1_2024!A8="jane doe") is an explicit operation and the cell must be exactly that value (i.e. capital letters, non-visible characters, extra spaces, etc... will all make it FALSE)
making the formula more 'general' for a range of inputs, adding it as a lookup or dynamic array may help but still need to know what you are trying to do besides the 1 sample calculation you gave. i.e. are you trying to create an output table with/for all the staff? do you have a list of staff or should we just use a UNIQUE(INDEX(Sheet1_2024!A8:A27,SEQUENCE(10,,,2))) to grab the unique list of employees. That said, we still need to fix / figure out why the 'basic' formula isn't working.- The cells match in text (ie, capital letters, spaces etc.)
There is a list of staff, it has staff name on row 1 and then their job title on row 2.
There is a top sheet that looks at the monthly calendar and tallies up the following (bn for bookings, holidays) this then is translated into a capacity %.
I have a sheet already that does this and is working, but when a staff member leaves/begins currently, I'm manually adding them in and changing the formulas, which takes time, so wanted to add a formula in that would look for the staff members name regardless of where they are on the sheet.SmitLaur930 I'm having some trouble knowing exactly the structure and what you need. My best guess is the following and attached.
on the left is a table with name, title on successive rows and the corresponding 2 rows of data.
based on that you are trying to count cases of *bn* in that data. I created a compound formula on the right that outputs names and counts.
Am I even close to what you need? Can you create a better sample sheet instead?