i have a COUNTIF function thats formula is as follows
=COUNTIF(Sheet3!AG:AG,A13)
however i want to condition it to exclude hidden cells once a filter is applied to column AG on sheet 3
i need some sort of mix between SUBTOTAL in which i can use 10X in order to exclude hidden cells but also be able to condition my search by counting all visible cells which matches the text in A13.
thanks in advance
Hi penfold1992,
Our Senior members have already provided a solution to this query.. see below one:-
http://www.excelforum.com/excel-gene...ered-list.html
Regards,
DILIPandey
<click on below 'star' if this helps>
DILIPandey, Excel rMVP
+919810929744 (India), +971528225509 (Dubai), dilipandey@gmail.com
thanks however im still unsure as to how to use this function:
=SUMPRODUCT(SUBTOTAL(3,OFFSET(A2,ROW(A2:A100)-ROW(A2),0)),(A2:A100="x")+0)
the 3 is the counting part but im not sure about what the rest means and what its used for....
with the formula listed in my first post i can change:
A2:A100="x"
to
Sheet3!AG:AG=A13
but other then that im pretty lost as to what to do to change this to fit my needs...
Edited for your scenario it becomesOriginally Posted by penfold1992
=SUMPRODUCT(SUBTOTAL(3,OFFSET(Sheet3!AG1,ROW(Sheet3!AG:AG)-ROW(Sheet3!AG1),0)),(Sheet3!AG:AG=A13)+0)
although using the whole column with SUMPRODUCT can be slow so you might want to use a more restricted range. In that formula the OFFSET part splits the column into individual cells so that SUBTOTAL can determine whether they are hidden or not. In the link from DILIP there's another link with a fuller explanation......
Audere est facere
another option would be to create a helper column that references the values your are filtering on, but exludes the filtered values...if(A1=value-for-filtering,"",A1)
and then base your coung on the helper column?
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
i apologize for my ignorance at this point but im still none the wiser as to solve this issue... there must be a way to do this...
would it be easier to put up an example spreadsheet to show exactly what i am trying to do? im trying to be as clear as possible.
i guess an example would be of a car registration database...
thousands of cars being processed through each day and you are logging each registration.
you then want a graph displaying the total sales of each month whilst showing the distribution of car manufacturers.
using this as a reference.... sheet3 would be the database.
=COUNTIF(Sheet3!AG:AG,A13)
then you would apply a filter of all Mercedes cars for example.
then on sheet1 i would have a cell that counts all the the cars registered in January.. another for February and so on.
Maybe will be usefull to take a look in this option, too.
http://www.excelforum.com/july-compe...-formulas.html
In this case you can use, just countif function.
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
im still not sure this is even a feasible method for what im trying to do...
converting a formula like:
=COUNTIF(Sheet3!AG:AG,A13)
just to exclude hidden cells is not too much of a task but when i also need to convert a formula such as:
converting this to exclude hidden cells is a lot of work and very taxing on the system (because dealing with large data is taxing as it is...)=SUM(COUNTIFS(Sheet3!AG:AG,A18,Sheet3!C:C,$L$11), COUNTIFS(Sheet3!AG:AG,A18,Sheet3!C:C,$L$12), COUNTIFS(Sheet3!AG:AG,A18,Sheet3!C:C,$L$14), COUNTIFS(Sheet3!AG:AG,A18,Sheet3!C:C,$L$15), COUNTIFS(Sheet3!AG:AG,A18,Sheet3!C:C,$L$16))
i guess the other method would be to try to filter the data BEFORE copying it to the spreadsheet but again im not sure if hidden cells are included in this... also i dont know an efficient way of filtering and copying...
there must be an easier way to do this and im currently getting frustrating into finding a way how -.-
is it possible to convert COUNTIFS in a similar fashion?
=COUNTIFS(sheet3!AG:AG,A13,sheet3!C:C,$L$10)
or is it not possible due to trying to make the "counting" even more defined?
To convert that you can use the earlier formula I suggested with an added condition, i.e.
=SUMPRODUCT(SUBTOTAL(3,OFFSET(Sheet3!AG1,ROW(Sheet3!AG:AG)-ROW(Sheet3!AG1),0)),(Sheet3!AG:AG=A13)*(sheet3!C:C=$L$10))
....but as I said before it will be slow applied to whole columns.
For the longer formula with multiple COUNTIFS is L13 deliberately left out for the last criteria, or do you have a COUNTIFS missing? To replicate "as is" try
=SUMPRODUCT(SUBTOTAL(3,OFFSET(Sheet3!AG1,ROW(Sheet3!AG:AG)-ROW(Sheet3!AG1),0)),(Sheet3!AG:AG=A18)*ISNUMBER(MATCH(sheet3!C:C,CHOOSE({1,2,3,4,5},$L$11,$L$12,$L$14,$L$15,$L$16),0)))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks