## Solution - Challenge 64 - Sum up the Range where a particular alphabet appears

Below is a possible solution to the **Challenge 64 - Sum up the Range where a particular alphabet appears**

=SUMPRODUCT(ISNUMBER(SEARCH(" "&C2&","," "&A2:A13&","))*(B2:B13))

Tue 15
Aug 2017

Below is a possible solution to the **Challenge 64 - Sum up the Range where a particular alphabet appears**

=SUMPRODUCT(ISNUMBER(SEARCH(" "&C2&","," "&A2:A13&","))*(B2:B13))

Sat 15
Jul 2017

Suppose, you have been given a range like this and you need to find the sum of column B where the alphabet "c" appears alone.

To check your answer, the sum will be 27 for above.

The file related to this challenge can be downloaded from **Challenge 64 - Sum up the Range where a particular alphabet appears**

The answer to the above solution will be presented after a month i.e. on 15-Aug-17.

Sat 11
Mar 2017

This checks your knowledge on Insert tab in Excel.

Sat 25
Feb 2017

In India, 2nd and 4th Saturdays are very important days as on these days, the banks remain closed. Hence, banks in India are closed on all Sundays and 2nd and 4th Saturdays apart from their holiday calendar.

To calculate Networkdays for banking industry in India needs that the list of 2nd and 4th Saturdays needs to be generated. You know the syntax of NETWORKDAYS -

NETWORKDAYS.INTL(Start_Date, End_Date, [Weekend],[Holidays])

In weekend parameter, we can specify Sunday as weekend and in Holidays list, we can put the holidays and also the 2nd and 4th Saturdays.

**Now, the challenge before you is to write a formula which takes Year as Input from A1 and which when dragged down produces the 2nd and 4th Saturdays.**

*Note - The solution to above problem will be published after a month i.e. on 25-Mar-17.*

Sat 18
Jun 2016

This time the challenge is - If a date is given, what would be the formula to find the number of days passed in a quarter.

If A1 has the value 12-Mar-16, then 31 days in Jan, 29 days in Feb (2016 is a leap year) and 11 days in Mar = 31+29+11 = 71 days have passed in Q1. Hence, answer is 71.

If A1 has 15-Apr-16, then 14 days have passed in Q2. Hence, answer is 14.

If A1 has 28-Aug-16, then, 31 days in Jul and 27 days in Aug = 31+27 = 58 days have passed in Q3. Hence, answer is 58.

You need to give the formula to find the above.

*The solution to above challenge will be published after a month i.e. on 18-Jul-16.*

Tue 14
Jul 2015

Below is a proposed solution for the challenge Challenge 15 – Floyd’s Triangle

Put this formula anywhere in your sheet and drag right and down

=IF(COLUMNS($A:A)>ROWS($1:1),"",IF(ROWS($1:1)=1,1,ROWS($1:1)-2+

COLUMNS($A:A)+INDIRECT(ADDRESS(ROW()-1,COLUMN()-COLUMNS($A:A)+1,1))))

The solution workbook is located at Solution - Challenge 15 – Floyd’s Triangle

Mon 13
Jul 2015

Below is a possible solution to the challenge -Challenge 4 – SUM of Multiplication of Preceding Digits by Succeeding Digits

Given your numerical string is in A1, use following formula -

=IFERROR(SUMPRODUCT((MID(A1,ROW(INDIRECT("1:"&LEN(A1)-1)),1)*MID(A1,ROW(INDIRECT("2:"&LEN(A1))),1))),0)

The answer would be as follows

Sat 06
Jun 2015

The Excel file related to this problem can be downloaded from **Challenge - Most Consecutive Appearance**

Suppose you have a table like below. The challenge before you is to find the sum of prize money if Y appears consecutively more than once for a person maximum time. You need to input name in F2 and sum of prize money should appear in F3. If name doesn't meet the criterion of consecutive Y maximum times, then Prize money should be blanks or any message can be put here.

Taking the example of Smith, most consecutive times Y appears in rows 7 to 10. For Doe, rows 15 to 16 and for Liz 19 to 21. Note for Smith, Y appears consecutively 3 times, first time in rows 2 and 3, second time in rows 7 to 10 and third time in rows 12 to 13. But since, we are talking about maximum consecutive appearance, hence we need to consider row 7 to 10.

If their are ties, pick up the first maximum consecutive appearance.

**Note** - Without the use of a helper column, it may be very difficult to solve. Hence, please feel free to use a helper column. Maximum allowed helper column is 0.

**Note - You may like to post your answer to this in comments.**

Sat 30
May 2015

Given the team list below, the challenge before you is to align teams RANDOMLY under Group A and Group B where one team faces another and there is no repetitions of the team. The formula should be flexible, in case, team list increases / decreases. The team list will always be even as if number of teams is odd, then one team will be left out.

The related Excel file can be downloaded from here **Team Pairing**

**Note** - You may choose to post your response in comments.