CODEWITHSUNDEEP
excelexcel-formulasumifs
Christina K
Christina K

Reputation: 33

Nesting SUMIFS in SUBTOTAL

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!

Upvotes: 2

Views: 6421

Answers (3)

bosco_yip
bosco_yip

Reputation: 3802

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

Upvotes: 1

thomasl
thomasl

Reputation: 191

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

Upvotes: 1

Joffan
Joffan

Reputation: 1480

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.

Upvotes: 1

Related Questions