# EXCEL FORMULA ERROR

Copper Contributor

# 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*")

13 Replies

# Re: EXCEL FORMULA ERROR

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

# Re: EXCEL FORMULA ERROR

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).

# Re: EXCEL FORMULA ERROR

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?

# Re: EXCEL FORMULA ERROR

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.

# Re: EXCEL FORMULA ERROR

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.

# Re: EXCEL FORMULA ERROR

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?

# Re: EXCEL FORMULA ERROR

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.

# Re: EXCEL FORMULA ERROR

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.

# Re: EXCEL FORMULA ERROR

@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?

# Re: EXCEL FORMULA ERROR

@mtarler Yes! thats what is needed.

Does that include if Jane Doe moved from the current location, to perhaps the bottom, the formula would still work, as it's searching for the name?

# Re: EXCEL FORMULA ERROR

yes.
If I recall correctly, right now it is using the UNIQUE of the name list as the list to output (the first column in the output) but that could be replaced with a different source if you need.

# Re: EXCEL FORMULA ERROR

Okay thank you! So using UNIQUE to help keep the names searchable?

# Re: EXCEL FORMULA ERROR

@SmitLaur930 So here is the formula I created and a quick break down:

=LET(in, A8:F21,
names, TAKE(WRAPROWS(TAKE(in,,1),2),,1),
UniqNames,UNIQUE(names),
data, WRAPROWS(TOROW(DROP(in,,1)),(COLUMNS(in)-1)*2),
out,BYROW(UniqNames,LAMBDA(r,SUM(--ISNUMBER(SEARCH("bn",TOROW(FILTER(data,names=r,""))))))),
HSTACK(UniqNames,out))

So line 1 is the range for the input data

line 2 takes the 1st column of the input range makes into 2  columns so that the 1st column is all names and the 2nd column is all the titles and then takes just the 1st column (names)

line 3 gets the unique list of names from line 2.  so if the same person is in the list multiple times it will combine those results.  you could easily replace the UNIQUE(names) with some other list if you only wanted the results from certain people

line 4 takes the data and similar to line 2 re-arranges it so every 2 lines of data are made into 1 line so that data lines up with the list of names from line 2

line 5 goes through each name in UniqNames (line 3) and finds the desired result

line 6 formats the output