[Solved] summarize the salary column

[Jululian]

Dear All,

May I kindly request that you amend or modify the formula to summarize the salary column as in (Yellow Cells)?
your help in this matter is highly appreciated
Regards

 

[Lutfor Rahman Shimanto]

Dear All,

May I kindly request that you amend or modify the formula to summarize the salary column as in (Yellow Cells)?
your help in this matter is highly appreciated
Regards

Dear Jululian

It's good to see you again. Thanks for sharing your problem with such clarity. I have reviewed your problem and devised a solution by enhancing your formula to reach your goal.

Follow these steps:

1. Select cell H2.
2. Insert the following formula:
   

   =CHOOSE({1,2},
   IF(IFNA(IF(ISTEXT((ROW(1:4)*500+1501)&"-"&((ROW(1:4))*500+2000)),FREQUENCY($B$2:$B$91, ROW(1:4)*500+2000), ""), "")<>"",(ROW(1:4)*500+1501)&"-"&((ROW(1:4))*500+2000),""),
   IFNA(IF(ISTEXT((ROW(1:4)*500+1501)&"-"&((ROW(1:4))*500+2000)),FREQUENCY($B$2:$B$91, ROW(1:4)*500+2000), ""), "")
   )

3. Hit Enter.
   

Hopefully, you have the formulas you were looking for. I have attached the solution workbook as well. Good luck.

Regards
Lutfor Rahman Shimanto
ExcelDemy

 

[Jululian]

Dear Jululian

It's good to see you again. Thanks for sharing your problem with such clarity. I have reviewed your problem and devised a solution by enhancing your formula to reach your goal.

Follow these steps:

1. Select cell H2.
2. Insert the following formula:
3. Hit Enter.
   View attachment 1405

Hopefully, you have the formulas you were looking for. I have attached the solution workbook as well. Good luck.

Regards
Lutfor Rahman Shimanto
ExcelDemy

The support you have given me is greatly appreciated and your assistance has had a significant impact thank you In the future, if I want to adjust the genius equation from 500 to 1000, what should I modify?

 

[Lutfor Rahman Shimanto]

The support you have given me is greatly appreciated and your assistance has had a significant impact thank you In the future, if I want to adjust the genius equation from 500 to 1000, what should I modify?

Dear Jululian

Thanks for your kind words! Your appreciation means a lot to us.

If you want the interval, for example, 501 to 1000, 1001 to 1500, and so on, you need to make some changes. Within the ISTEXT function, first, you must modify ROW(1:21) so that it iterates through rows 1 to 21 (it was 1:4 previously). Next, add "500+1" to get the intended intervals. Within the FREQUENCY function, make adjustments likewise for rows but add "500+500". So, get the point: adjustment will vary from the interval and dataset you use.

Don't worry! I have modified the previously given formula for your desired intervals. Please check the following:

1. Select cell D2.
2. Insert the following formula:
   

   =CHOOSE({1,2},
   IF(IFNA(IF(ISTEXT((ROW(1:21)*500+1)&"-"&((ROW(1:21))*500+500)),FREQUENCY($B$2:$B$91, ROW(1:21)*500+500), ""), "")<>"",(ROW(1:21)*500+1)&"-"&((ROW(1:21))*500+500),""),
   IFNA(IF(ISTEXT((ROW(1:21)*500+1)&"-"&((ROW(1:21))*500+500)),FREQUENCY($B$2:$B$91, ROW(1:21)*500+500), ""), "")
   )

3. Hit Enter.
   

Hopefully, these ideas will help you. Stay blessed.

Regards
Lutfor Rahman Shimanto
ExcelDemy

 

[Jululian]

Dear Jululian

Thanks for your kind words! Your appreciation means a lot to us.

If you want the interval, for example, 501 to 1000, 1001 to 1500, and so on, you need to make some changes. Within the ISTEXT function, first, you must modify ROW(1:21) so that it iterates through rows 1 to 21 (it was 1:4 previously). Next, add "500+1" to get the intended intervals. Within the FREQUENCY function, make adjustments likewise for rows but add "500+500". So, get the point: adjustment will vary from the interval and dataset you use.

Don't worry! I have modified the previously given formula for your desired intervals. Please check the following:

1. Select cell D2.
2. Insert the following formula:
3. Hit Enter.
   View attachment 1406

Hopefully, these ideas will help you. Stay blessed.

Regards
Lutfor Rahman Shimanto
ExcelDemy

Thank you for the support you've given and your assistance has had a significant impact Many thanks on your great performance

 

[Lutfor Rahman Shimanto]

Thank you for the support you've given and your assistance has had a significant impact Many thanks on your great performance

Dear Jululian, Thank you for your kind words! You are very welcome. We are glad the solution works for you and has a significant impact.

Regards
ExcelDemy