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