Return a value over a number of columns

[ianxv1100]

Hi



I hope someone can point me in the right direction, I feel that this should be easy but everything I have tried has failed

I have a spreadsheet Columns A – L

Column A is Name Q1 cards

Column B is Available?

Column C is Senior Selected

Column D is Name Q2 Cards

Column E is Available?

Column F is Senior Selected

Column G is Name Q2 Cards

Column H is Available?

Column I is Senior Selected

Column J is Name Q2 Cards

Column K is Available?

Column L is Senior Selected



Columns M N and O are called

Summary Name, Summary Available, Senior Selected



What I am seeking to achieve is to check all the names over columns A,D, G & J and where if all four results Available under Columns B, E,H, & K return a Yes then Yes input against the name. If any say No then return No. The same for Senior Selected but if Available is correct then Senior Select will just copy over I hope.



I have tried Match/Index, XLOOKUP, VLOOKUP and a few others which are very trial and error and all return results of 0 or N/A or just fail.



Any pointers gratefully received
REgards
Ian

 

[hagia_sofia]

To understand it correctly: are you interested in a) if there there are 4 occurences of "Yes" within a single row or b) if there are 4 occurences of individual names across those 4 columns?

 

[ianxv1100]

To understand it correctly: are you interested in a) if there there are 4 occurences of "Yes" within a single row or b) if there are 4 occurences of individual names across those 4 columns?

Hi,

I want to return a value of yes if I have 1 or more instances of yes against the same name over the four Quarters Q1,2,3,4. If I have a no shown then I wish to return a No

Q1 names are the entrants in a golf competition as are Q2,3 & 4 some people may play in 1 or more competitions hence Q2 names may have Q1 names in etc

Thanks
Ian

 

[hagia_sofia]

I would recommend you to structure your data differently, i.e. to add a new column "Name Cards" which would contain data from columns A, D, G, J, i.e. to stack all the data vertically. Then you can easily identify unique names via UNIQUE function in an auxiliary table since they will be in one column, then use COUNTIFS function to count the number of occurences for each name and then use any lookup function with IF function for the original table.

 

[ianxv1100]

I would recommend you to structure your data differently, i.e. to add a new column "Name Cards" which would contain data from columns A, D, G, J, i.e. to stack all the data vertically. Then you can easily identify unique names via UNIQUE function in an auxiliary table since they will be in one column, then use COUNTIFS function to count the number of occurences for each name and then use any lookup function with IF function for the original table.

Hi Thanks for that I will give it a spin and see how I get on