formula that will give a value depending on the group of the given value

[mark692]

good day is there a formula for a column that will give me a value on column REMARKS. In my sample I have column G for girls and column B for boys column C is their group and column D is the remarks where I want to put a formula. if column A names is within a group Remarks will give me text "Girls", like on my example Anna Marie and Juliet is within Group A thats why remarks give me text "Girls", as you can see in my example cell A5 has no name and because it is within group a i want it also to have Girls on remarks. thanks

Book1
	A	B	C	D
1	G	B	Group	Remarks
2	Anna		A	Girls
3	Marie		A	Girls
4	Julet		A	Girls
5			A	Girls
6		Ben	B	Boys
7		Ron	B	Boys
8			B	Boys
9			B	Boys
10	Liza		C	Combined
11		Arthur	C	Combined
12			C	Combined
Sheet1

 

[sandy666]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Remarks = Table.AddColumn(Source, "Remarks", each if ([G] <> null or [G] = null) and [Group] = "A" then "Girls" else if ([B] <> null or [B] = null) and [Group] = "B" then "Boys" else "Combined")
in
    Remarks

G	B	Group		G	B	Group	Remarks
Anna		A		Anna		A	Girls
Marie		A		Marie		A	Girls
Julet		A		Julet		A	Girls
		A				A	Girls
	Ben	B			Ben	B	Boys
	Ron	B			Ron	B	Boys
		B				B	Boys
		B				B	Boys
Liza		C		Liza		C	Combined
	Arthur	C			Arthur	C	Combined
		C				C	Combined

this can be easily translated into a worksheet formula

 

[AlanY]

try this in D2 and copy down

=IF(COUNTIFS($A$2:$A$12,"<>"&"",$C$2:$C$12,C2)>0,IF(COUNTIFS($B$2:$B$12,"<>"&"",$C$2:$C$12,C2)>0,"Combined","Girls"),"Boys")

 

[mark692]

try this in D2 and copy down

=IF(COUNTIFS($A$2:$A$12,"<>"&"",$C$2:$C$12,C2)>0,IF(COUNTIFS($B$2:$B$12,"<>"&"",$C$2:$C$12,C2)>0,"Combined","Girls"),"Boys")

thank you sir this will do!

 

[AlanY]

you're welcome