i use SUMIF but i want to see only filtered data

[akalyptos_2000]

Hi!

i am very beginner in excel i dont know many things :D

i have this formula =SUMIF(N12:N4997,"oikistiko",G12:G4998)


it works good but when i filter an other cell it counts all the cells with oikistiko inside

i want to have results only the cells i see after filters

with search i find out that i must go to SUBTOTAL formula.. i try without success

please help :D

Thanks!

 

[jasonb75]

SUBTOTAL only works as a lone function if you're filtering the data so that only rows that contain "oikistiko" are visible. If you're filtering on a different criteria then you would need to use both SUBTOTAL and SUMIF.

The simplest way is to add a helper column to get the subtotal of each individual row, then use sumif with the helper column. There are ways to do it without a helper column if necessary but the formula needed will be much more complicated and less efficient.

Assuming that column O is currently empty, enter this formula into O12 and fill it down to O4997

=AGGREGATE(9,5,G12)

Then change your SUMIF formula to

=SUMIF(N12:N4997,"oikistiko",O12:O4997)

Note that if column O is not empty and you use a different one for the AGGREGATE formula then you will need to change the last part of the SUMIF formula to the actual column that you use.

AGGREGATE works in similar ways to SUBTOTAL but is more flexible with what it can be used for. When SUBTOTAL is used for what you are doing, the last row will not be hidden by the filter if it doesn't meet the criteria, with AGGREGATE it will.

 

[akalyptos_2000]

THANKS DUDE!!!

working like a charm!

10 days i try to find it :D

thanks for your fast reply!