Summarizing no. of orders from the different sheets to a table

[Ben9108]

Hi Everyone

I would like to summarize the number for each type of order in the different sheets of each month in a table. Would anyone be able to help me develop the formulas? Thank You

Enclosed a file

[AliGW]

Are you still using Excel 2010?

There are NO expected results in the workbook - mock up results for JANUARY, please - do this manually.

[Ben9108]

Thanks for your reply

I attached a revised Excel file with sample results for your information. Hope you can help me. Thanks

[Ben9108]

Thanks AliGW

I updated the Excel file with mock-up results. Would you mind helping me to solve the issues? Thanks

[JeteMc]

This produces different amounts from those originally shown, especially for the PM work type, so I feel that we will need further information.
1. Unmerge the cells in the Trade column and fill in the blanks
2. Use the following formula for the Jan column: =SUMPRODUCT(('JAN 2024'!C$13:DS$32=B12)*(ISNUMBER(SEARCH(C12,'JAN 2024'!D$13:DT$32))))
Note that the original amounts are in column Q.
Let us know if you have any questions.

[Glenn Kennedy]

1. I get the same results as JeteMc. If they are incorrect, we need more explanation.

2. This solution uses a Named Range (called Trade, CTRL-F3 to view/edit):
=LOOKUP(ROW($B$12:$B$27),ROW($B$12:$B$27)/($B$12:$B$27>0),$B$12:$B$27)

3. This allows you to keep the merged cells. They look pretty, but they are not easy to handle in Excel.

4. The formula below can be ciopied across and dnown to do the lot.
=IFERROR(SUMPRODUCT((INDIRECT("'"&D$11&" "&$D$10&"'!C13:DS32")=INDEX(Trade,ROWS(D$12:D12)))*(ISNUMBER(SEARCH($C12,INDIRECT("'"&D$11&" "&$D$10&"'!D13:DT32"))))),"")

5. A word of Warning. INDIRECT is volatile. It recalculates EVERY time ANYTHING changes. On big sheets this can cause performance issues.

[Ben9108]

Thanks Glenn, JeteMc, AliGW

The problems has been solved. Many Thanks

[AliGW]

Glad to have helped.

If you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.