EXCEL FORMULA

Question

EXCEL FORMULA

Lesa Sanders 20

012024.xml Here is my delima. I need a formula that will look at K2:k1057 4LMD86 and then count how many 141 are in f2:f1057 for that staff id then I will do the same for duration User's image

Lesa Sanders 20 Reputation points

2024-02-14T20:14:46.2133333+00:00

I sorted and then copied each id into a new sheet and then did a countif and this works but I want to be able each month to copy spread sheet into this master workbook and it automatically look each id up and service code and duration. I can do it in separate sheets on my workbook. I know just enough of excel to be dangerous. lol I don't know if this makes sense.
Jiajing Hua-MFST 16,440 Reputation points Microsoft External Staff

2024-02-21T08:54:41.0866667+00:00
Hi @Lesa Sanders

Sorry I may have overlooked this comment.

but I want to be able each month to copy spread sheet into this master workbook and it automatically look each id up and service code and duration.

If you need a high degree of automation, you will need the help of VBA code.

The suggestions I provide here still require some manual efforts.

Use Power Query to import data from different ID sheets. After some data processing, append the queries as a new one and load them to the Master sheet.

To get the data from id sheet, you may use Indirect formula, for example, the data of "4BRP81", the formula can be =INDIRECT("'"&A1064&"'!C200:Z201").

Then use SUMIFS formula to get duration for different ID.

If you have further questions, you can let us know.

1 answer

Your answer

Lesa Sanders 20 Reputation points

2024-02-14T20:14:46.2133333+00:00

I sorted and then copied each id into a new sheet and then did a countif and this works but I want to be able each month to copy spread sheet into this master workbook and it automatically look each id up and service code and duration. I can do it in separate sheets on my workbook. I know just enough of excel to be dangerous. lol I don't know if this makes sense.
Jiajing Hua-MFST 16,440 Reputation points Microsoft External Staff

2024-02-21T08:54:41.0866667+00:00

Hi @Lesa Sanders

Sorry I may have overlooked this comment.

but I want to be able each month to copy spread sheet into this master workbook and it automatically look each id up and service code and duration.

If you need a high degree of automation, you will need the help of VBA code.

The suggestions I provide here still require some manual efforts.

Use Power Query to import data from different ID sheets. After some data processing, append the queries as a new one and load them to the Master sheet.

To get the data from id sheet, you may use Indirect formula, for example, the data of "4BRP81", the formula can be =INDIRECT("'"&A1064&"'!C200:Z201").

Then use SUMIFS formula to get duration for different ID.

If you have further questions, you can let us know.

Answer 1

Jiajing Hua-MFST 16,440 Microsoft External Staff

Hi @Lesa Sanders

Please try the formula =SUMIFS(H2:H1057,K2:K1057,"4LMD86",F2:F1057,"141"). Or the formula based on your sample =SUMIFS(H2:H1057,K2:K1057,$A$1061,F2:F1057,C$1060).

enter image description here

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Share via

EXCEL FORMULA

1 answer

Your answer