Writing a SUMIFS formula in Excel

[redbaron131]

Please see the attached picture for most cell locations.

I am creating a work tracker to capture metrics for hours and values for various reports and logs for individuals.

I have been attempting to figure out how to create the needed formula. If there is potentially an easier way, I am all ears.

Example of how I want it the formula (seen below) to behave:

`M34:M38` all have `123test123`

`J34` is filled with `“Name1”`

`G34:G38` all contain different values to sum

The summed values are then returned to the cell that has the formula that has `“Name1”` in `P3``("*"&P3&"*")`

`M39:M48` all have `456test456`

`J39` is filled with `“Name2”`

`G39:G48` all contain different values to sum

The summed values are then returned to the cell that has the formula that has `“Name2”` in `P4``("*"&P4&"*")`

This process will continue until the end of the range.

`G` and `M` will have data in all cells. `J` and `O` will only have data in the first cell of the instance of the unique JCN.

The formula will be in W3 for the individuals in `J`
A similar formula will be in X3 for the individuals in `O` but need to validate they are not in both `J` and `O`. I want the data to be counted only once per person.

I hope what I just explained makes since. If not please ask and I will try my best to clarify

 

[zero269]

Please see the attached picture for most cell locations.

I am creating a work tracker to capture metrics for hours and values for various reports and logs for individuals.

I have been attempting to figure out how to create the needed formula. If there is potentially an easier way, I am all ears.

Example of how I want it the formula (seen below) to behave:

`M34:M38` all have `123test123`

`J34` is filled with `“Name1”`

`G34:G38` all contain different values to sum

The summed values are then returned to the cell that has the formula that has `“Name1”` in `P3``("*"&P3&"*")`

`M39:M48` all have `456test456`

`J39` is filled with `“Name2”`

`G39:G48` all contain different values to sum

The summed values are then returned to the cell that has the formula that has `“Name2”` in `P4``("*"&P4&"*")`

This process will continue until the end of the range.

`G` and `M` will have data in all cells. `J` and `O` will only have data in the first cell of the instance of the unique JCN.
View attachment 86214
The formula will be in W3 for the individuals in `J`
A similar formula will be in X3 for the individuals in `O` but need to validate they are not in both `J` and `O`. I want the data to be counted only once per person.
View attachment 86213

I hope what I just explained makes since. If not please ask and I will try my best to clarify

Welcome to the forum...
As a new member, I'd first recommend using this forums Excel Add-In to make sharing your sample data a lot easier...
The Add-In is here:

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

 

[awoohaw]

I've recreated your workbook. But I really have no idea what you want in terms of calculations. Can you provide some additional information, and what the expected values are?

 

[redbaron131]

I've recreated your workbook. But I really have no idea what you want in terms of calculations. Can you provide some additional information, and what the expected values are?

Thank you for helping me to come up with a solution to my problem.
I am having an issue using XL2BB, I have it installed but it continues to crash Excel when attempting to copy a part of the table.
In the example screenshot on my original post.

All the values that are in `G` that are in the same row as identical JCNs in `M` will be returned to the table that is in P:Y using the the type of unique identifier "*"&P3&"*" etc for each name.
If the name is in the crew chief `J` column the sum of the values will be returned to W3 etc....
If the name is in the crew member column the sum will be returned to X3 etc...
As this sheet is going to be filled out by my staff members, I want to be able to give them credit once for each JCN. If the name appears in both `J` and `O` it will only give credit to `J`.

So to clear it up.

In the example, the yellow/blue/green are all separate JCN tracked work tasks.
Data in `M` is used to identify the matching rows in `G `and the crew chief/crew members.
The sum of the rows in `G`, will be return to the table in `P:Y` to all the names in `J` and `O` that are highlighted in yellow/blue/green.
(Note: the highlighting is only for the example, the cells will not be highlighted in the functional sheets.)

The number of row that are contained in the JCN will never be a set number. It can be a single row or 100 rows.
Either way the data in `J` and `O` will only be populated in the first instance of the JCN.

I hope this helps and does not cause any further confusion.

 

[awoohaw]

Well, I'm still not sure what result you want to have? Do you want a list? A dollar amount? How is either calculated if you were doing it manually? Your description helps get the ingredients, but I don't know what you want to serve on the table.

 

[redbaron131]

Sorry for the confusion.

All I wish to do is sum and return the dollar amount to the individual names listed for each row of G that has matching JCNs to their respective row in the second table on the same sheet.

 

[redbaron131]

Sorry for the confusion.

All I wish to do is sum and return the dollar amount to the individual names listed for each row of G that has matching JCNs to their respective row in the second table on the same sheet.

If I was to manually perform the task I would need to find all identical JCNs in M, sum all of the values in G for that JCN, and input that value into each row based on the individual on the crew.

I would then need to perform that task for each of the various JCNs in M.

I would then need to sum all the dollar values for each JCN for each individual and that will be the final total for the month.