Countif- Visible Cells in Filter mode

[aabbasi]

Hi:

Could someone please let me know the solution for the following problem:

I have values in cell B2:B50 with values such as Quality, Eng, Purchasing etc. When i go in Auto filter based on Coulmn A2:A50 (with July only) and count "Quality" manually under B2:B50, the answer is 26. But if I write formula Countif(B2:B50, "Quality") I get answer 41.

Is there a way to use Countif function, if I am in the Autofilter mode so as it counts only that rows which are visible under Autofilter and not ALL rows.

Thank you

 

[mastabog]

This has been a very informative thread. The initially posted formula works for me but I'd like to add additional conditions. For the original one, i.e.

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B50,ROW(B2:B50)-MIN(ROW(B2:B50)),,1))*(B2:B50="Quality"))

how do I add another condition for a different column? For example, in addition to column B containing "Quality" I would like to also condition on column C being greater than 9 (column C has numbers). How does one adapt the above?

I did try a few things but couldn't get it to work ... I admit, I'm quite new to this part of Excel.

Cheers

 

[Aladin Akyurek]

This has been a very informative thread. The initially posted formula works for me but I'd like to add additional conditions. For the original one, i.e.

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B50,ROW(B2:B50)-MIN(ROW(B2:B50)),,1))*(B2:B50="Quality"))

how do I add another condition for a different column? For example, in addition to column B containing "Quality" I would like to also condition on column C being greater than 9 (column C has numbers). How does one adapt the above?

I did try a few things but couldn't get it to work ... I admit, I'm quite new to this part of Excel.

Cheers

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B50,ROW(B2:B50)-MIN(ROW(B2:B50)),,1))*(B2:B50="Quality")*(C2:C50>9))

We also often write:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2,ROW(B2:B50)-ROW(B2),0),--(B2:B50="Quality"),--(C2:C50>9))

 

[mastabog]

Darn, was just about to come post that i solved it after reading this page: http://www.xldynamic.com/source/xld.SUMPRODUCT.html#performance

Thanks, though!

Is there a way to speed up computations when executing that kind of formula multiple times knowing that 2 of the arrays out of 3 do not change?

I have a dataset of about 200,000 rows in a table and need to execute that kind of =SUMPRODUCT about 300 times. It's really slow even on an Intel 3770K @ 4.9 GHz, about 30-60 seconds of computation.

I tried defining two variables in the Formulas panel (Define name):

filtered = SUBTOTAL(3,OFFSET(B2:B50,ROW(B2:B50)-MIN(ROW(B2:B50)),,1))
hist = --(Data[name]="History")

then use them as =SUMPRODUCT(filtered, hist, --(Data[marks]>A2)) where A2 will change ... but it's not any faster.

I's also like to avoid VBA. Any ideas?

 

[Aladin Akyurek]

Darn, was just about to come post that i solved it after reading this page: http://www.xldynamic.com/source/xld.SUMPRODUCT.html#performance

Thanks, though!

Is there a way to speed up computations when executing that kind of formula multiple times knowing that 2 of the arrays out of 3 do not change?

I have a dataset of about 200,000 rows in a table and need to execute that kind of =SUMPRODUCT about 300 times. It's really slow even on an Intel 3770K @ 4.9 GHz, about 30-60 seconds of computation.

I tried defining two variables in the Formulas panel (Define name):

filtered = SUBTOTAL(3,OFFSET(B2:B50,ROW(B2:B50)-MIN(ROW(B2:B50)),,1))
hist = --(Data[name]="History")

then use them as =SUMPRODUCT(filtered, hist, --(Data[marks]>A2)) where A2 will change ... but it's not any faster.

I's also like to avoid VBA. Any ideas?

What is the name of the sheet which houses the data?

 

[mastabog]

The sheet's name is "Sheet1". The table name is "Data" (I have my data formatted as a table called "Data"). Why do you ask? Is it any faster if I reference sheet cells rather than table columns?

Also, this may sound silly but I couldn't get this to work: how do I condition on cells containing the sub-string "History" rather than being equal to "History". For example, I'd like cells "History 101" and "World History" both to match. I tried using (SEARCH("History",Data[name])>0) or things like that but didn't work, neither did (Data[name]="*History*") ... any clues?

In any case, I really appreciate your time.

 

[Aladin Akyurek]

The sheet's name is "Sheet1". The table name is "Data" (I have my data formatted as a table called "Data"). Why do you ask? Is it any faster if I reference sheet cells rather than table columns?

Also, this may sound silly but I couldn't get this to work: how do I condition on cells containing the sub-string "History" rather than being equal to "History". For example, I'd like cells "History 101" and "World History" both to match. I tried using (SEARCH("History",Data[name])>0) or things like that but didn't work, neither did (Data[name]="*History*") ... any clues?

In any case, I really appreciate your time.

Why don't we do first a regular formula?

The sheet is Sheet1.

What are the exact ranges and the conditions that must hold for each?

 

[mastabog]

I have a ton of ranges and conditions that are at the discretion of the user of the workbook. they are free to choose different filters and conditions. I'm not quite sure what you are asking though. Consider this example:

- the table is 200,000 rows and 31 columns
- table is called "Data"
- column Data[name] needs to contain the string "History", as a substring
- column Data[mark] needs to be less than some value N_max (this is variable, but consider it fixed for now, say 5.0)
- counting needs to respect the auto-filters that are set on the rows of the table (can be any of them), which is why SUMPRODUCT()+SUBVALUE() are needed

What I have now is:

=SUMPRODUCT(filtered, hist, --(Data[mark]<5))

where

filtered = SUBTOTAL(3,OFFSET(A2,ROW(Data[id])-ROW(A2),0))
hist = --(Data[name]="History")

are defined names

[ I don't know yet how to match the substring "History" ]

Computing that SUMPRODUCT is quite slow on this big sheet, about 6-10 seconds ... I don't know why though I could live with it if it was just once. Now consider I have 100 different values for N_max in a column outside the table (or another sheet)... unless the auto-filters change, there is no need to recompute the SUBVALUE() and the array for "History", yet Excel seems to do that ... defining the names "filtered" and "hist" doesn't speed things up. Any ideas to make it faster?

Cheers.

p.s. Matlab in comparison is blistering fast, a fraction of a second ... but my colleagues are not Matlab literate, which is why I'm trying to do it in Excel (I also like the auto-computation and auto charts features of Excel whenever I change the auto-filters, so I'd really like to get it done in Excel).

 

[mastabog]

[ deleted double post, sorry ]

 

[mastabog]

Sorry, the defined name filtered = SUBTOTAL(102, OFFSET(Data[id],ROW(Data[id])-MIN(ROW(Data[id])),,1,1)) which works fine. The others work too.

 

[Aladin Akyurek]

I have a ton of ranges and conditions that are at the discretion of the user of the workbook. they are free to choose different filters and conditions. I'm not quite sure what you are asking though. ...

My objective was a simple list in order to forward a suggestion.

Why don't we do first a regular formula?

The sheet is Sheet1.

What are the exact ranges and the conditions that must hold for each?

Let's suppose...

Sheet: Sheet1
Area: A1:F100 with A1:F1 housing the headers.
Task: Count all items containing NAD in D2:D100 while AutoFilter is applied to the area.

Define FVec using Formulas | Name Manager as referring to:

=ROW(Sheet1!$A$2:$A$100)-ROW(Sheet1!$A$2)

Option 1

=SUMPRODUCT(SUBTOTAL(3,OFFSET($D$2,Fvec,0)),--ISNUMBER(SEARCH("NAD",$D$2:$D$100)))

Probably, the following would work also...

Define FilterRef as referring to:

=OFFSET(Sheet1!$A$2,Fvec,0)

Option 2

=SUMPRODUCT(SUBTOTAL(3,FilterRef),)),--ISNUMBER(SEARCH("NAD",$D$2:$D$100)))

Option 3

Select the area, convert it into a table, then define Fvec and FilterRef and build the suggested formulas.

Hope such yields some performance gain.