Summing filtered columns by certain criteria

[the man]

Hi there,

I'm having an issue of finding a way to sum certain "profits" based on a variable/ criteria while filtered. I tried using Sumif function, but when data gets filtered it still sums the "hidden" data. is there a way to create a formula that changes the sum value when filtered.

for example- in the image attached- I'm trying to determine the sum of all "profit" values when I have filtered for abc and cba excluding bca

 

[SpillerBD]

SUBTOTAL will sum Filtered data.
Hidden rows will still be included.
Yes, there is a fine difference between filtering and hiding rows.

 

[Aladin Akyurek]

Try the following:

=SUMPRODUCT(SUBTOTAL(9,OFFSET(B2,ROW(B2:B13)-ROW(B2),0)),(A2:A13="abc")+0)

 

[the man]

However, subtotals won't allow me to sum only abc data, if i slect abc and cba it will show the subtotal for both, disregarding that I only want to see the sum of ABC while filtering this way

 

[Fluff]

If you use
=SUBTOTAL(109,your range)
It will ignore both filtered & hidden rows

 

[the man]

Try the following:

=SUMPRODUCT(SUBTOTAL(9,OFFSET(B2,ROW(B2:B13)-ROW(B2),0)),(A2:A13="abc")+0)

Thanks this seems to work, now I just have to wrap my small brain around how to implement it in a larger scale

 

[Aladin Akyurek]

Thanks this seems to work, now I just have to wrap my small brain around how to implement it in a larger scale

You are welcome. Note that you can have a condition like abc in a cell of its own and refer to that cell in the formula instead.

Let's insert a few rows in front of current row 1.

Let A1 house the abc value.

In B1 then you can have:

=SUMPRODUCT(SUBTOTAL(9,OFFSET($B$8,ROW($B$8:$B$19)-ROW($B$8),0)),($A$8:$A$19=$A1)+0)​

 

[SpillerBD]

On a Large Scale (or even a small one) using Pivot Tables is far simpler and quicker way of getting these answers and having product/item comparisons side-by-side.

 

[CA_Punit]

If I use Index and Match function in place of Offset it is returning value error.

How to determine when to use offset function and when to use Index and Match.

Info: I have this confusion because both functions return cell references.

 

[Aladin Akyurek]

If I use Index and Match function in place of Offset it is returning value error.

How to determine when to use offset function and when to use Index and Match.

Info: I have this confusion because both functions return cell references.

The subpart of the formula with OFFSET, that is,

OFFSET($B$8,ROW($B$8:$B$19)-ROW($B$8),0)

returns as desired a set of values (in fact, a set of subranges), not a single value. INDEX (with MATCH) would instead just deliver a single value.​

Select this part of formula on the formula bar and apply F9 in order to see what this bits computes.