Count cells on different sheet

[eechristaylor]

I am using COUNTA to look at a range of cells on another sheet and if they have data getting them to return the number. That is the "Status" field.

I need to do this across several rows and use the unique identifier to make sure the correct row data is returned. Example below and across those cells in Red. Is there a way to use xlookup for this?
Sheet 1

Unique identifier	Status
A	3​
B	2​
C	2​

Sheet 2

Unique identifier	Date 1	Date 2	Date 3	other	other	Date 4	other	other	Date 5
A	3/1/2023​	5/24/2023​				7/30/2024​
B	3/1/2023​	5/24/2023​
C	3/1/2023​	5/24/2023​

 

[awoohaw]

what cell in sheet2 do you want the counta() function used? How is the status number used in Sheet 2? (are you appending the value to the word "Date"?) Can you explain more?

 

[eechristaylor]

what cell in sheet2 do you want the counta() function used? How is the status number used in Sheet 2? (are you appending the value to the word "Date"?) Can you explain more?

I want it to be used in all the Date fields that are red. I am bringing this to a master sheet so i can judge project completion. If all 5 fields have a date and the sheet one will reflect it as "5" we know that project is complete. These dates on the project all have a different meaning, so for now i have Counta(sheet2!B2:D2, sheet2!g2, sheet2!J2) but this means the data has to stay specifically in row 2. I want to be able to ensure the count for "A" is the same no matter what row it is on in sheet2.

where the "status" shows 3,2,2 this is where I need the formula.

 

[awoohaw]

i'm still a bit confused. But it sound like you want to a count of the date columns that are populated.
If so, try this: (this whinged, so not entirely sure it will work perfectly - but the minisheet below works as I think you want - you just need to adjust the references.).

=SUM(--ISNUMBER(ChooseCols(FILTER(Sheet2!$B$2:$K$100,Sheet2!$A$2:$A$100=Sheet1!$A2,""),2,3,4,7,10)))

Book4
	A	B	C	D	E	F	G	H	I	J
1	Unique identifier	Status
2	A	3
3	B	2
4	C	2
5
6	Sheet 2
7	Unique identifier	Date 1	Date 2	Date 3	other	other	Date 4	other	other	Date 5
8	A	2023-03-01	2023-05-24				2024-07-30
9	B	2023-03-01	2023-05-24
10	C	2023-03-01	2023-05-24
11
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=SUM(--ISNUMBER(CHOOSECOLS(FILTER($A$8:$J$10,$A$8:$A$10=A2,""),2,3,4,7,10)))

 

[eechristaylor]

i'm still a bit confused. But it sound like you want to a count of the date columns that are populated.
If so, try this: (this whinged, so not entirely sure it will work perfectly - but the minisheet below works as I think you want - you just need to adjust the references.).

=SUM(--ISNUMBER(ChooseCols(FILTER(Sheet2!$B$2:$K$100,Sheet2!$A$2:$A$100=Sheet1!$A2,""),2,3,4,7,10)))

Book4
	A	B	C	D	E	F	G	H	I	J
1	Unique identifier	Status
2	A	3
3	B	2
4	C	2
5
6	Sheet 2
7	Unique identifier	Date 1	Date 2	Date 3	other	other	Date 4	other	other	Date 5
8	A	2023-03-01	2023-05-24				2024-07-30
9	B	2023-03-01	2023-05-24
10	C	2023-03-01	2023-05-24
11
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=SUM(--ISNUMBER(CHOOSECOLS(FILTER($A$8:$J$10,$A$8:$A$10=A2,""),2,3,4,7,10)))

Thanks but that did not work, I input formula in my sample document and no matter what the input is it only returns a "1"