2

I have a table like this:

Activity    Month Budget     Planned EUR 
Activity 1  January          € 1,000.00 
Activity 1  January          € 2,000.00 
Activity 1  February         € 2,000.00 
Activity 2  January          € 1,200.00 
Activity 3  January          € 1,200.00 
Activity 2  March            € 560.00 
Activity 2  February         € 700.00 
Activity 3  January          € 6,000.00 
Activity 3  January          € 80.00 
Activity 3  March            € 900.00 
Activity 3  March            € 540.00 
Activity 4  January          € 455.00 
Activity 1  February         € 500.00 
Activity 2  March            € 3,500.00 
Activity 4  March            € 600.00 
Activity 5  January          € 1,000.00 
Activity 4  February         € 2,200.00 
Activity 2  January          € 500.00 
Activity 3  March            € 600.00 
Activity 3  February         € 570.00 
Activity 1  March            € 150.00

I would like to be able to calculate the sum of Planned EUR for all the activities, splitted but the month, but also to exclude from the totals, the rows that get filtered out

Something like that:

          January         February        March
          € 13,435.00    € 5,970.00      € 6,850.00

I have managed to calculate the sum by using the SUMIFS folmula. Please see below the calculation: =SUMIFS($G$7:$G$27,$F$7:$F$27,F$2)

But I cannot exclude from the total, the rows that get filtered out. Could you please advice? Is there a way I can use nesting SUMIFS in SUBTOTAL formula?

Please find in attach the screenshot from the excel file for a better understanding.

Thank you in advance!

Christina K
  • 33
  • 1
  • 4
  • I know it's a bit glib, but would a pivot table work better for your needs? Of course I don't know how you're filtering but some of that can be done in pivot tables too. – Joffan May 26 '21 at 17:04

3 Answers3

1

Add a column with a formula to detect whether the row is hidden or not, see picture below. The grey text shows what formula is used left of it. In the illustrated example the SUBTOTAL-function returns 1 if the row is visible, and 0 if hidden by filter (which you wont see of course). Add that column to your SUMIFS like shown in the bottom of the picture.

In this case I used "3" as first argument in in the SUBTOTAL-function. You can change this to 103 if you want the function to be sensitive to manually hidden rows (not only by filter). Play around with it and you'll get the picture.

You can hide the helping column if you'd like - the subtotal are not sensitive to column visibility regardless of the first argument of SUBTOTAL.

I hope this works for you

SUBTOTALIFS

Thomas Lilleng
  • 171
  • 5
  • That's a devious use of the subtotal function, nice workaround +1 – Joffan May 26 '21 at 17:25
  • Thanks! I could add that I find that this works best along with a structured table. That way one won't have to maintain the extra column when rows are added, especially if the helping column is hidden, making it easy to overlook. – Thomas Lilleng May 26 '21 at 17:41
1

This should be a good job for a pivot table, unless your filtering requirements are too complex to handle this way (in which case Thomas Lilleng's formula dodge would make it possible again).

The result of making a pivot table on your sample data above looks like: pivot table

where I have added an "other data" column to filter the whole table results - this is not as flexible as the filters in a spreadsheet but allows selection of chosen values, which is where (if necessary) it could interact with Thomas' formula.

Joffan
  • 1,485
  • 1
  • 13
  • 18
1

Formula solution without helper column

In E3, formula copied across right :

=SUMPRODUCT(($F$7:$F$27=E2)*SUBTOTAL(9,OFFSET($G$7,ROW($F$7:$F$27)-ROW($F$7),)))

enter image description here

bosco_yip
  • 3,762
  • 2
  • 5
  • 10