SUMIF sum_range

Emmet McKenna

Hello, I am trying to calculate the SUM in numerous columns based on if the Level Column contains certain text;

e.g. SUMIF Level = L04 then SUM Column CAB 900 and I have this working with the formula =SUMIF(Level:Level, "L04", [CAB 900]:[CAB 900])

The problem I am having is that I will have 100+ columns and have to do this for L04, L03, L02, L01, L00. Is there a way to make the sum_range the current column containing the formula no mater the name? Something along the lines of below;

=SUMIF(Level:Level, "L04", "@Column")

Emmet McKenna

Hi Paul, your first suggestion works perfectly. I can dragfill the Sum/Children formula across and it shows correct for all columns. Thanks very much for your help on this.

Paul Newcome

If you lock the column reference for the Level column, you should be able to dragfill and have the [Cab 900] reference update.

=SUMIF($Level:$Level, "L04", [CAB 900]:[CAB 900])

Emmet McKenna

Hi Paul, thanks for the quick reply. Unfortunately when I put this formula in the CAB 900 column and then dragfill across it keeps the [CAB 900]:[CAB 900] reference in all columns, so all columns show the same SUM total.

Another issue I am having is that when I enter another formula =SUMIF($Level:$Level, "L03", [CAB 900]:[CAB 900]) changing L04 to L03 it shows;

#CIRCULAR REFERENCE in the cell with the L04 Formula and;

#BLOCKED in the cell with the L03 Formula

Paul Newcome

The dragfill should be working, I use it a lot to quickly populate formulas across columns and down rows. Exactly what are you clicking on/doing to dragfill?

The circular reference is expected. You can only have one formula in a column that references the column it is in. Any more than that creates the circular reference. Are you able to provide a screenshot of your sheet and some details on how it works along with the end goal of capturing these totals?

Emmet McKenna

For the dragfill, I just entered the Formula in the cell highlighted above, then clicked on the blue dot at the bottom right of the cell and dragged it across to the right.

This screenshot shows a portion of the sheet, the Level column, can be one of L00, L01, L02, L03, L04 (Dropdown, single select). My aim is that if a certain Room has L04 it counts the CAB types and provides a total quantity for L04 for that CAB type. Then same for L03 and so on. The top row is for overall total quantities. Thanks

Paul Newcome

Ok. What is the reason for pulling this data? Is it for display purposes within the sheet, or can it be displayed in a different sheet? Are you setting up metrics that will eventually be displayed on a dashboard? Are you open to a slight adjustment to the layout of this sheet if these metrics must stay on the sheet?

Dragfill: It may be that it is not working because of the formula referencing the column it is not residing in. Once we determine the above questions regarding purpose and layout, we can work on that part.

Emmet McKenna

The project is phased by Level so we need to be able to see total quanties per Level. Ideally it would be better if it could stay on this sheet and I'd be open to adjusting to allow this. I don't intend to display metrics on a Dashboard at present.

Paul Newcome

Ok. There are a couple of ways this can be restructured to allow for everything to stay on the same sheet, and they both involve hierarchy.

Option 1 would be grouping each item underneath of it's corresponding parent level row and using =SUM(CHILDREN()) in the parent rows.

The second option is to maintain something more like your current format but nest the Level totals under the Totals parent row and then nest the items under an item parent row then use the same =SUM(CHILDREN()) in the top Totals row, and for each of the individual level totals use

=SUMIFS(CHILDREN([CAB 900]$5), CHILDREN($Level$5), @cell = "L04")

and change the "L04" accordingly.

Emmet McKenna

Thanks, I'll have a look at these options.

Emmet McKenna

Hi Paul, your first suggestion works perfectly. I can dragfill the Sum/Children formula across and it shows correct for all columns. Thanks very much for your help on this.

Paul Newcome

Happy to help. 👍️