Help needed for Lookup function

[Solveig]

Hello!
I have a problem with my excel sheet and I hope someone can help me here!

In column A I have unique names. In column B-H is the days and in column I is the total. Every time a name appears on a day, an Y appears, and the number of Y's are counted on the last row. The problem is that for the different names there are different numbers of rows containing Y's - so how can I make a function that returns the row of totals for each of the names? (I don't need the Y's.) Is it possible to make the function work even if I delete all the content and replaces it with updated content (of course in the same format)? Thank you in advance for the help!

 

[Scott Huish]

Hi, welcome to the board!

If I understand you correctly, your setup looks something like this:

Excel Workbook
	A	B	C	D	E	F	G	H	I
1		Mon	Tue	Wed	Thu	Fri	Sat	Sun	Total
2	Name	Y	Y	2
3		Y	Y	2
4		Y	1
5		Y	1
Sheet1

This would be extremely easier to calculate if you had the name on each row

Excel Workbook
	A	B	C	D	E	F	G	H	I
1		Mon	Tue	Wed	Thu	Fri	Sat	Sun	Total
2	Name	Y	Y	2
3	Name	Y	Y	2
4	Name	Y	1
5	Name	Y	1
Sheet1

 

[texasalynn]

Hello!
I have a problem with my excel sheet and I hope someone can help me here!

In column A I have unique names. In column B-H is the days and in column I is the total. Every time a name appears on a day, an Y appears, and the number of Y's are counted on the last row. The problem is that for the different names there are different numbers of rows containing Y's - so how can I make a function that returns the row of totals for each of the names? (I don't need the Y's.) Is it possible to make the function work even if I delete all the content and replaces it with updated content (of course in the same format)? Thank you in advance for the help!

create a pivot table with the count of the Y's

 

[snoopyhr]

Just a shot in the dark, if it works, great

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Names	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Total
2	Name-1	y						y	2
3	Name-2	y		y		y		y	4
4	Name-3	y	y		y	y	y		5
5	Name-4	y			y	y		y	4
6	Name-5	y	y	y			y	y	5
7	Name-6	y	y	y		y			4
8	Name-7	y	y	y	y	y	y		6
9	Name-8	y		y		y	y	y	5
10	Name-9	y			y	y	y		4
11	Name-10	y	y		y	y		y	5
12	Name-11	y	y	y	y	y	y		6
Sheet4

 

[Solveig]

Hotpepper, you are right - the only thing is that under all the Y's, there is a new rows with all the totals (sum of Y's.), and the name is only stated one time at the first row.

The problem is at for instance, at for name number 1 there is 5 rows of y's and 1 row of total, while for name number 2 there is only 3 rows of y's and 1 row of total.

I would like to return only the name + the rows of totals into a new sheet.

Is this possible?

 

[Scott Huish]

That's what I thought at first, but the op said this:

The problem is that for the different names there are different numbers of rows containing Y's

 

[Solveig]

Hotpepper, you are right - the only thing is that under all the Y's, there is a new rows with all the totals (sum of Y's.), and the name is only stated one time at the first row. (and there are no numbers along with the y's like you had in your model)

The problem is at for instance, at for name number 1 there is 5 rows of y's and 1 row of total, while for name number 2 there is only 3 rows of y's and 1 row of total.

I would like to return only the name + the rows of totals into a new sheet.

Is this possible?