Excel : Filter Functions List !

[ionelz]

I am using on my Spreadsheet functions like : SUM , COUNTIFS , COUNTIF
They do not work with Filter (data will not update)
So I replaced SUM with SUBTOTAL(SUM, ....)

01. How can I replace COUNTIF and COUNTIFS to work with Filter ?
02. Can you tell me the Excel Functions List which work with Filter ?

[Pete_UK]

Use Excel Help (press the F1 key) to search for SUBTOTAL - you will see that the first parameter is a function number in the range 1 to 11 (or 101 to 111) which determines the function which you want to use. SUM is 9, AVERAGE is 1, MAX is 4, and so on. The numbers 1 to 11 will include manually-hidden rows whereas the numbers 101 to 111 will exclude them - both sets of numbers will exclude filtered-out rows. There is no equivalent function number for COUNTIFS, COUNTIF, as you can include the criteria for the filter within the conditions.

Hope this helps.

Pete

[ionelz]

So SUBTOTAL is the only function which works with Filtered-rows ?
How can I make COUNTIFS , COUNTIF work with Filtered-rows ?

[Pete_UK]

COUNTIF can only be used for one condition, so you might not be able to set that up, but COUNTIFS can be used for multiple conditions. You need to specify what you want to achieve - which column(s) have a filter condition applied to them and what are the conditions, which column is the COUNTIFS to apply to, and what condition will apply in that case?

You can also use SUMPRODUCT to give you a conditional count or sum, but again you include the filter criteria within the formula.

Pete

[ionelz]

Ok, I want to count in a Table01 , how many in Column Range (A1:A100) IF in Column Range (B1:B100) = "NAME"
Actually, "NAME" is = cell value in another TABLE02
I used COUNTIF

But when I Filter the Table01 (Filter in Column C) COUNTIF will not update
How can I solve this ?

[ionelz]

In other words, I am looking for similar function like SUBTOTAL , COUNT (but COUNT with criteria !!!)

[Tony Valko]

Ok, I want to count in a Table01 , how many in Column Range (A1:A100) IF in Column Range (B1:B100) = "NAME"
Actually, "NAME" is = cell value in another TABLE02
I used COUNTIF

But when I Filter the Table01 (Filter in Column C) COUNTIF will not update
How can I solve this ?

This is very confusing.

How many "what" in Column Range (A1:A100)?

[Pete_UK]

And what filter condition are you applying for column C ?

Pete

[ionelz]

Ok, how can I replace this :

=SUMIF(A1:A10,"TEXT_A",B1:B10)

=COUNTIFS(D1:D10,"TEXT_B",E1:E10,">0")

SO IT WORK WITH FILTER

[Tony Valko]

Try these...

=SUMPRODUCT(SUBTOTAL(9,OFFSET(B2,ROW(B2:B10)-ROW(B2),0)),--(A2:A10="Text_A"))

=SUMPRODUCT(SUBTOTAL(3,OFFSET(D2,ROW(D2:D10)-ROW(D2),0)),--(D2:D10="Text_B"),--(E2:E10>0))

[ionelz]

Thank you,
It may work, I have to see ...
But for example, column range B2:B10 I replaced with NAME_B (Created by selection)
How to replace B2 ?
How would I say B2 is second row in NAME_B ?

[ionelz]

=SUMIF(A1:A10,"TEXT_A",B1:B10)

=SUMPRODUCT(SUBTOTAL(9,OFFSET(B2,ROW(B2:B10)-ROW(B2),0)),--(A2:A10="Text_A"))

I can not make this one to work right !

[ionelz]

Sorry, I was able to make all the formulas to work
Thanks so much.
If I can get an answer to this question : (posted before)

But for example, column range B2:B10 I replaced with NAME_B (Created by selection)
How to replace B2 ?
How would I say B2 is second row in NAME_B ?

Also, when I COPY the FORMULAS in Column

=SUMPRODUCT(SUBTOTAL(9,OFFSET(B2,ROW(B2:B10)-ROW(B2),0)),--(A2:A10="Text_A"))

=SUMPRODUCT(SUBTOTAL(3,OFFSET(D2,ROW(D2:D10)-ROW(D2),0)),--(D2:D10="Text_B"),--(E2:E10>0))


for some reason (and I don't get it) B2 became B3 , in my opinion the Range should stay

[Tony Valko]

But for example, column range B2:B10 I replaced with NAME_B (Created by selection) How to replace B2 ? How would I say B2 is second row in NAME_B ?

Sorry, I don't understand.

Also, when I COPY the FORMULAS in Column

=SUMPRODUCT(SUBTOTAL(9,OFFSET(B2,ROW(B2:B10)-ROW(B2),0)),--(A2:A10="Text_A"))

=SUMPRODUCT(SUBTOTAL(3,OFFSET(D2,ROW(D2:D10)-ROW(D2),0)),--(D2:D10="Text_B"),--(E2:E10>0))

for some reason (and I don't get it) B2 became B3 , in my opinion the Range should stay

If you want a particular reference to remain constant (also known as absolute) then you use the $ dollar sign:

$A2 = absolute column
A$2 = absolute row
$A$2 = absolute column and row

[ionelz]

=SUMPRODUCT(SUBTOTAL(9,OFFSET(B2,ROW(B2:B10)-ROW(B2),0)),--(A2:A10="Text_A"))

B2 is second row in Column B2:B10

B2:B10 is NAME_B so formula became (I have Created NAME_B for this Column)

=SUMPRODUCT(SUBTOTAL(9,OFFSET(B2,ROW(NAME_B)-ROW(B2),0)),--(A2:A10="Text_A"))

But when I copy the formula, B2 change to B3, B4, ....and a want to freeze it to B2
Range stay, condition A2:A19 change
That is why I have asked, how can I freeze B2 even if table move to other location

[ionelz]

I was looking for $A$2

[ionelz]

Thanks, I was looking for $A$2 ....

[Tony Valko]

You're welcome!