Sum values in a column, if another column has a specific value "value changed dynamically"

Example file attached.
Please note that I will copy this formula in several other tables, so please use table headers in the formula, so I can copy and edit it for other places.
Calculation will be done only for the rows that have Classification in column E.
I tried this formula

Formula:

=SUMIF(G2:G26,[@[Classification Serial]],G2:G26)

in G27 but it gives 0, while it should give 547, which is the sum of the following values:
64
86
119
68
111
99

Please note that this example only has numbers from 1 to 5 for classifications, but it could be extended to 20 or 40, in all cases, the number in column F for the statement will match the number in column F for classifications.
This is why I said it is a dynamic one.

Try:

Formula:

=SUMIFS(G$2:G$26,$F$2:$F$26,Table1[@[Classification Serial]:[Classification Serial]])

copied across.

You can't use Structured Table references for columns F and G. That would cause circular references, I think.

Thanks a lot for the fast reply.
It worked like a charm my friend
If your time allows, please, may you share how you think about it and did it, as I am learning :D
Thanks indeed!

I followed the formula you did and I was able to understand the 1st part related to sum_range and the 2nd part related to criteria_range1, but the 3rd part related to criteria1 I was not able to understand it, so please if your time allow to clear it that would be awesome.

It could be just

Formula:

=SUMIFS(G$2:G$26,$F$2:$F$26,$F27)

copied down and across

Table1[@[Classification Serial]:[Classification Serial]] is the equivalent of making column F absolute. The @ sign refers to the current row. I only included that because you said include Table headers. As I said, you can't do that for the first two parts.

It's an unusual way to set up a Structured Table. One of the benefits of a Structured Table is that it will propagate formulae. You can't do that here.

Thanks for the rep.

I've marked it as solved because it is indeed.

So, to refer to the current row in a table you just use the

Formula:

@

and add a column header between

Formula:

[]

and add the

Formula:

:

then add the column header once again between

Formula:

[]

, did I understood it correctly?

Please, now that I added more data, I've realized that I need to edit the formula manually (which makes sense).

Because I am not working alone in the file, my coworker at work may forget to edit the formula.

I've googled a lot for a solution to make it as dynamic as possible, and I found that it could be done using the INDEX and the OFFSET functions, but I was not able to adjust it.

I mean you thankfully build the formula depending on the manual range from G$2:G$26; so I was thinking about, whether is it possible to set this range using column E and use the functions INDEX, FILTER, and OFFSET or a similar function like v or xlockup to look for the column E and look for the word statement and then take the cell associated with it from the column G ...etc and that would be the range.

That may achieve the goal of only selecting the cells to the range that has cell contents equal to "Statement", then after that, we take the associated cells in the same range but in column F.

I mean we determine the range of the cells we are searching within and do the sum according to the cell contents of the column E.

So Is it doable to determine the range for column F according to the cell contents of column E, and only consider cells that have the "Statement" then we do the sum we did earlier?

The purpose of the extra requirement is that it will not require manual editing if the range changes.

I hope that I was able to express myself.

@moderators: So I thought to ask if should I add these extra requirements here or if I should start a new thread.

Fine here, but remove the SOLVED tag.

@AliGW, Thanks a lot, my friend
@TMS, I am sorry for bothering you

If I understand the issue, then it seems to me that one fix would be to separate the input and output tables.
In the output table columns G:L are populated using: =SUMIFS(tbl_Input[2016],tbl_Input[[Classification Serial]:[Classification Serial]],tbl_Output[@[Classification Serial]:[Classification Serial]])
Let us know if you have any questions.

Originally Posted by JeteMc

Formula:

=SUMIFS(tbl_Input[2016],tbl_Input[[Classification Serial]:[Classification Serial]],tbl_Output[@[Classification Serial]:[Classification Serial]])

This is working, but I am having a tough time trying to understand some of its parts so I can apply these techniques elsewhere because I am dealing with a lot of tables, and using the

@

:

[]

seems critical keys for several operations when building formulas for tables.

So back to the formula above, I followed it and started adding it manually to understand.

Please, let me divide the formula into its main sections:

2023-10-07 00 49 36.png

• SUMIFS ... this is the formula string.
  
• sum_range ... this is the 1st part of the formula that represents the range that needs to be summed, which is tbl_Input[2016].
  
• ,Then we add the 1st separator which is the comma ... , .
  
• criteria_range1Then we add the 2nd part of the formula sections which represent criteria_range1, which is tbl_Input[Classification Serial]. Now I am confused with the extra :[Classification Serial]] you added "line #7 in the above image, so what is the purpose of that addition?
  
• @ Now you are adding the last part of the formula that contains @tbl_Output[@[Classification Serial]:[Classification Serial]], so now you are an extra @ before the name of the table!? (why? isn't it supposed to be like the previous part? and only be tbl_Output[Classification Serial] ?
  
• Also why 2 extra @ ? one before the table name, and the 2nd before column name?
  
• Also why column name repeated between the :
  

I hope you or other members clear these points for me in order to get a better understanding.

@JeteMc
I will follow your advice and separate tables. It worked great

@TMS
Thanks for the original solution.

Note: I've marked it as solved, and separating the table was the easy way to do so, but to my knowledge was it doable using other formulas? just for the sack of learning.

Try:

Formula:

=SUMIFS(G$2:G$26,$F$2:$F$26,Table13[@[Classification Serial]:[Classification Serial]],$E$2:$E$26,"Statement")

@TMS
Your original formula worked correctly, I was just wondering if there is a way that I may achieve the result without using any manual ranges like

Formula:

G$2:G$26

or like, but just want to use dynamics ranges which I can select the whole column as a range in table column, and add some conditions to that range, so if anything changed, the range should be updated dynamically. I am just trying to avoid manual formula editing later on when the range is being extended. This is why I wondered about using OFFSET, INDEX, MATCH, FILTER, VLOCKUP ...etc formulas.
The logic in my mind is as follows:

1. Select the entire column range for the column titled "Classification/Statement"
2. Select only the range that has "Statement".
3. Select the associated range in the column titled "Classification Serial".
4. Then select the range from step #3 to be used in the SUMIFS formulas.
5. This way I will not use the manual $2 or $26 etc
6. I mean to use only table ranges like
   Formula:

   [[Classification Serial]:[Classification Serial]]

   , this way will make things much easier.
7. So to set the range and then edit it using the offset or filter or index or match or combinations of them all etc.

This is why I asked if there is a way to use other formulas besides the one being used which is SUMIFS.
Did I express it correctly?

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

I was just wondering if there is a way that I may achieve the result without using any manual ranges

No, I don't think so.

Originally Posted by TMS

No, I don't think so.

Thanks a lot

Good questions.
Notice that when you move the cursor from cell G30 to cell H30 the sum range changes from tbl_Input[2016] to tbl_Input[2017] which is fine.
However, we would not want criteria range 1 to change so using tbl_Input[[Classification Serial]:[Classification Serial]] is the way to lock the column and row when using structured (table column name) references equivalent to typing $F$2:$F$26 when using A1 references.
Similarly in criteria 1, tbl_Output[@[Classification Serial]:[Classification Serial]] locks the column but not the row equivalent to $F30
This tutorial may also be helpful: https://www.excelcampus.com/tips-sho...0%20and%202013.
By the way, I do not get an extra @ in front of the name of the table. That may be a difference between the way the 2019 and 365 versions of Excel display the formula.
Let us know if you have any questions.

I've reviewed the equations once again and copied the whole sheet into another one and renamed tables to Table1 for inputs and Table2 for outputs, and did added the equation from scratch as shown in the video you thankfully linked and it turned out that it like this:

Formula:

=SUMIFS(Table1[2016],Table1[[Classification Serial]:[Classification Serial]],Table2[@[Classification Serial]:[Classification Serial]])

It is much easier to do it according to the technique shown in the video.
Also I realized that no @ before table name "I do not know what it was exist earlier".
I learned a lot

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