ExcelJet Challenges

=IF(SEARCH("Y",B5),"MWF")

=IF(SEARCH("Y",B6),"TT")

=IF(SEARCH("Y",B7),"MTWTF")

search is utilized to find the substring y in the selected cell for each formula. If returns the specified strings if the conditions in search is met. This is applied to all formulas.

=SUM(COUNTIF(item,"Tshirt"),COUNTIF(item,"Hoodie"))

The nested countif counts all the cells in the selected range of cells where the string is t-shirt or hoodie. Sum adds the results of each countif in the nested countif.

=SUMPRODUCT(ISNUMBER(MATCH(item,{"Tshirt","Hoodie"},0))*ISNUMBER(MATCH(color,{"Red","Blue","Green"},0)))

=SUMPRODUCT(ISNUMBER(MATCH(item,{"Tshirt","Hoodie"},0))*ISNUMBER(MATCH(color,{"Red","Blue","Green"},0))*ISNUMBER(MATCH(city,{"Denver","Seattle"},0)))

Match checks to see if the strings in the curly brackets are found in the selected range of cells and returns 1 for cells where the condition is met and an error message when the condition isn’t met. Isnumber converts the 1’s into True and the error messages into False. Sumproduct multiples all the matching cells and then finds the sum of the product. The result of each sum product is multiplied because the multiplication asterik works as an or operator in Excel.

=IF(item="Tshirt",1,IF(item="Hoodie",1,0))

=COUNTIFS(C3#,1,E3#,1)

=IF(color="Red",1,IF(color="Blue",1,IF(color="Green",1,0)))

=COUNTIFS(C3#,1,E3#,1)

=IF(city="Denver",1,IF(city="Seattle",1,0))

=COUNTIFS(C3#,1,E3#,1,G3#,1)

The ifs create a column where 1 is added to cells where the values in the selected range of cells match the specified strings in the if expressions. 0 is assigned to cells where the values in the selected range don’t include the specified strings. The countifs count all the cells in the newly created column where the value is 1.

=INDEX(H5:H23,MATCH(G5,C5:C23,0))

Match checks to see if the condition is met, in this if the value in lookup cell G5 is found in the lookup range of cells. Index returns values in the selected range of cells if the condition created by match is met.