counting cells in filtered list

I have a 3 column spreadsheet, the third column is conditionall
formatted and the spreadsheet has an automatic filter on each column.
I have the following formula at the bottom of column C which gives m
the total count of cells in column C or the total resulting from th
filter applied in Column A =SUBTOTAL(3,C3:C83).

Below that I have a summary 
Split	No.	%
RED	47	58.02%
AMBER	27	33.33%
GREEN	7	8.64%

I need to the results in the No. column of the summary to reflect th
filtered list not the whole spreadsheet - at the moment the formla fo
RED under No is =COUNTIF(C3:C83,"<10") but this counts all cells no
those filtered.  Can you help

--
rayda
-----------------------------------------------------------------------
raydaw's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3298
View this thread: http://www.excelforum.com/showthread.php?threadid=52808

0
3/30/2006 1:36:39 PM
excel 39879 articles. 2 followers. Follow

10 Replies
565 Views

Similar Articles

[PageSpeed] 37

Try

=SUMPRODUCT((C3:C83<10)*(SUBTOTAL(3,
OFFSET($A$2,ROW($A$3:$A$83)-ROW($A$1),,1))))

-- 
 HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"raydaw" <raydaw.25hj0m_1143726001.1615@excelforum-nospam.com> wrote in
message news:raydaw.25hj0m_1143726001.1615@excelforum-nospam.com...
>
> I have a 3 column spreadsheet, the third column is conditionally
> formatted and the spreadsheet has an automatic filter on each column.
> I have the following formula at the bottom of column C which gives me
> the total count of cells in column C or the total resulting from the
> filter applied in Column A =SUBTOTAL(3,C3:C83).
>
> Below that I have a summary
> Split No. %
> RED 47 58.02%
> AMBER 27 33.33%
> GREEN 7 8.64%
>
> I need to the results in the No. column of the summary to reflect the
> filtered list not the whole spreadsheet - at the moment the formla for
> RED under No is =COUNTIF(C3:C83,"<10") but this counts all cells not
> those filtered.  Can you help?
>
>
> -- 
> raydaw
> ------------------------------------------------------------------------
> raydaw's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=32982
> View this thread: http://www.excelforum.com/showthread.php?threadid=528087
>


0
bob.phillips1 (6510)
3/30/2006 3:20:16 PM
Thanks for the help Bob but unfortunately it didn't work.  Formula is
still counting all cells in list - even those hidden.


-- 
raydaw
------------------------------------------------------------------------
raydaw's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=32982
View this thread: http://www.excelforum.com/showthread.php?threadid=528087

0
3/30/2006 4:29:26 PM
I tested it on my data and it worked, so there must be something in the
data, or my interpretation of it.

Can you post the data, or maybe send me a workbook.

-- 
 HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"raydaw" <raydaw.25hqw0_1143736204.6819@excelforum-nospam.com> wrote in
message news:raydaw.25hqw0_1143736204.6819@excelforum-nospam.com...
>
> Thanks for the help Bob but unfortunately it didn't work.  Formula is
> still counting all cells in list - even those hidden.
>
>
> -- 
> raydaw
> ------------------------------------------------------------------------
> raydaw's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=32982
> View this thread: http://www.excelforum.com/showthread.php?threadid=528087
>


0
bob.phillips1 (6510)
3/30/2006 4:50:37 PM
Try this

=SUMPRODUCT(--($C$3:$C$83<10),SUBTOTAL(3,OFFSET($A$2,ROW($A$3:$A$83)-MIN(ROW($A$3:$A$83)),,1)))

if that does not work then your values are not what you think, OTOH Bob's 
formula should not return all rows. It has a typo in that includes the 
header (A2 instead of A3) and

ROW($A$3:$A$83)-ROW($A$1)

should be

ROW($A$3:$A$83)-MIN(ROW($A$3:$A$83)

however it should not count all rows
-- 

Regards,

Peo Sjoblom

http://nwexcelsolutions.com




"raydaw" <raydaw.25hqw0_1143736204.6819@excelforum-nospam.com> wrote in 
message news:raydaw.25hqw0_1143736204.6819@excelforum-nospam.com...
>
> Thanks for the help Bob but unfortunately it didn't work.  Formula is
> still counting all cells in list - even those hidden.
>
>
> -- 
> raydaw
> ------------------------------------------------------------------------
> raydaw's Profile: 
> http://www.excelforum.com/member.php?action=getinfo&userid=32982
> View this thread: http://www.excelforum.com/showthread.php?threadid=528087
> 


0
Peo
3/30/2006 5:04:59 PM
Thank you both for your help.  The formula works great now!

However, here is an additional problem for you.  I also need to count
the filtered cells in C3:C83 which have values between (and including)
10 and 16.  At present the array formula is
=SUM(IF((F3:F83<=16)-(F3:F83<10),1,0)) which works fine but counts the
hidden rows.

I love your formula but don't understand it well enough to adapt (what
is the purpose of the -- at the start?

Sorry to be thick!


-- 
raydaw
------------------------------------------------------------------------
raydaw's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=32982
View this thread: http://www.excelforum.com/showthread.php?threadid=528087

0
4/3/2006 1:35:00 PM
In article <raydaw.25oxga_1144071301.3755@excelforum-nospam.com>,
 raydaw <raydaw.25oxga_1144071301.3755@excelforum-nospam.com> wrote:

> =SUM(IF((F3:F83<=16)-(F3:F83<10),1,0)) which works fine but counts the
> hidden rows.

Try...

=SUMPRODUCT(SUBTOTAL(3,OFFSET(F3:F83,ROW(F3:F83)-ROW(F3),0,1)),--(F3:F83>
=10),--(F3:F83<=16))

>... what is the purpose of the -- at the start?

Have a look at the following link...

http://www.mcgimpsey.com/excel/formulae/doubleneg.html

Hope this helps!
0
domenic22 (716)
4/4/2006 4:11:05 AM
See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed
explanation.

-- 
 HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"raydaw" <raydaw.25oxga_1144071301.3755@excelforum-nospam.com> wrote in
message news:raydaw.25oxga_1144071301.3755@excelforum-nospam.com...
>
> Thank you both for your help.  The formula works great now!
>
> However, here is an additional problem for you.  I also need to count
> the filtered cells in C3:C83 which have values between (and including)
> 10 and 16.  At present the array formula is
> =SUM(IF((F3:F83<=16)-(F3:F83<10),1,0)) which works fine but counts the
> hidden rows.
>
> I love your formula but don't understand it well enough to adapt (what
> is the purpose of the -- at the start?
>
> Sorry to be thick!
>
>
> -- 
> raydaw
> ------------------------------------------------------------------------
> raydaw's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=32982
> View this thread: http://www.excelforum.com/showthread.php?threadid=528087
>


0
bob.phillips1 (6510)
4/4/2006 8:53:23 AM
HELP - situation is becoming urgent - can anyone tell me how to count
cells with values between 10 and 16 (inclusive) in a filtered list
without including hidden rows


-- 
raydaw
------------------------------------------------------------------------
raydaw's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=32982
View this thread: http://www.excelforum.com/showthread.php?threadid=528087

0
4/4/2006 9:25:43 AM
Have you tried the formula I offered?

In article <raydaw.25qgry_1144143000.6184@excelforum-nospam.com>,
 raydaw <raydaw.25qgry_1144143000.6184@excelforum-nospam.com> wrote:

> HELP - situation is becoming urgent - can anyone tell me how to count
> cells with values between 10 and 16 (inclusive) in a filtered list
> without including hidden rows
0
domenic22 (716)
4/4/2006 9:58:21 AM
Dominec

Thank you very much for your help - it works!

--
rayda
-----------------------------------------------------------------------
raydaw's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3298
View this thread: http://www.excelforum.com/showthread.php?threadid=52808

0
4/4/2006 11:00:26 AM
Reply:

Similar Artilces:

how to copy the same cell across different work books into another workbook easily?
i have the daily sales from 1.xls, 2.xls likewise till 31.xls, in a single folder. now i have a final consolidated workbook called final.xls, wherein i would want to copy the same cell across all the workbooks into final.xls(which is again in the same folder) easily,..someway like the fill handle or is there someother way, other than selecting each time the cell to be linked??? can anyone help, this is really breaking my head,,.... -- sageerai ------------------------------------------------------------------------ sageerai's Profile: http://www.excelforum.com/member.php?action=getinfo...

macro to move cursor one cell right
If I need to move the cursor to the right to paste a value copied from another sheet , what macro command should I use You don't need to. The Copy Method accepts a Range argument which would be the destination for the paste operation. For example: Worksheets(1).Range("A1").Copy Destination:=3DWorksheets(3).Range("B12") --JP On Sep 22, 3:31=A0pm, Kodak1993 <Kodak1...@discussions.microsoft.com> wrote: > If I need to move the cursor to the right to paste a value copied from > another sheet , what macro command should I use If JP's reply does not do...

Change a cell's fill color dynamically?
Is it possible to lookup a value in a cell over here and change a cell's fill color over there based on certain criteria? For instance, if all the workdays for a month are listed in column A, is it possible to look up all the Fridays and change the corresponding cell in Column C from whatever color to Yellow? While I'm at it, is it possible to unlock those certain C cells for editing, as well? Thank You so very much. Arlen You can handle the color issue with conditional formatting; you don't need any lookup function. As far as locking/unlocking the cells, you probably ...

Bank reconsolidation/Create Outstanding list
Is there a template or macro that can match downloaded bank information with general ledger information exported to excel. Then create an outstanding list and list of discrepancies. Thank you. If your downloaded bank information is in the form of a text or csv fil then both can be imported directly to Excel. Comparison then is relatively easy if both sets of information star with a date field for instance -- Message posted from http://www.ExcelForum.com Hi Sam! It's unlikely that there will be such a template because different banks record the data in different ways. If you can co...

How can I check a cell for current date and insert it if blank?
I am modifying the invoice template. I want to create a formaula that checks the date cell for the current date. If a date occupies the cell nothing happens, otherwise, the current date is inserted. Can this be done? Thanks Don Sub insertdate() With ActiveCell If Not IsDate(.Value) Then .Value = Date End With -- Don Guillett SalesAid Software dguillett1@austin.rr.com "Don K" <Don K@discussions.microsoft.com> wrote in message news:85927992-1BA5-4B6A-94A7-AFFCCB607BD7@microsoft.com... >I am modifying the invoice template. I want to create a formaula that >checks >...

Protecting cell contents
I have about 5 cells on my sheet that contain formulas which reference other sheets. I therefore want to prevent users from accidentally changing or deleting these 5 formulas. They are free to modify any other data on the sheet. How can I preserve these 5 cells which cointain formulas without using the "Protect Sheet" options??? BTW, users have the ability to protect and unprotect this sheet at any time with their own passwords, so it seems like I need another way to protect my formulas. I mean, once the user un-protects the sheet, they are able to delete anything an...

How do I set up a Count Down Timer in Publisher?
How do I set up a count down timer in Publisher? Are you doing a web page? Ask your question in microsoft.public.publisher.webdesign -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Dan in Sa" <Dan in Sa@discussions.microsoft.com> wrote in message news:22231322-AB6D-4AED-BFAE-B147481D0AFF@microsoft.com... > How do I set up a count down timer in Publisher? ...

Counting dates using "more than"
Hi all This is hard to explain so please bare with me I would like to count in a column with dates. The formula should count or group the periods when the gap between th dates are more than 3. (days) 1-May-04 4-May-04 5-May-04 6-May-04 7-May-04 11-May-04 14-May-04 15-May-04 19-May-04 In other words; 1,4,5,6,7 is one period 11,14,15 is another and 19 is another With the total being 3 Thanks in advance Joey:confused -- Message posted from http://www.ExcelForum.com Assuming the dates are always in sequential order and the range of dates in this case is A1:A9: =IF(SUMPRODUCT(--(A2:A10-A...

Cell Styles on Ribbon
The context window that should "pop-up/out" when I click on Cell Styles on the Home tab is "locked" on to my ribbon. Normal, Bad, Good, etc. is on one line and Check Cell, Explanatory, Input, etc is on the second row. I looks like something that you would do if you wanted to modify a ribbon. What is your question? -- HTH Bob "Stephen J" <Stephen J@discussions.microsoft.com> wrote in message news:D1F8E067-FD46-442F-81BF-9F28AFE465BE@microsoft.com... > The context window that should "pop-up/out" when I click on Cell Style...

IF clause for a range of cells
The following formula works well for me but is there a way to make it shorter by defining a range of cells, rather than individual cells, in the IF part? =SUM(G8:AJ8)+IF(G8="",4)+IF(H8="",4)+IF(I8="",4)+IF(J8="",4)+IF(K8="",4)+IF(L8="",4)+IF(M8="",4)+IF(N8="",4)+IF(O8="",4)+IF(P8="",4)+IF(Q8="",4)+IF(R8="",4)+IF(S8="",4)+IF(T8="",4)+IF(U8="",4)+IF(V8="",4)+IF(W8="",4)+IF(X8="",4)+IF(Y8="",4)+IF(Z8=&quo...

Selection List box in a cell?
How do I use one sheet to allow the user to enter text in each cell of a column. Then in another sheet convert it to a selection list in one cell ? Hutch, You can accomplish that using Data|Validation|Allow List. The List reference should be to a Named Range where the user inputs their data. You should be able to use a dynamic range that would allow for a variable list length using something similar to the formula below as the Name Definition =OFFSET($A$1,0,0,COUNTA($A:$A) To create the named range. (This formula would assume that there is no row heading and that there would be no o...

why cut and insert cells only works randomly?
It seems that cutting and inserting cells in the spreadsheet, errors every other time and locks the excell spread sheet... Hi Tony, You'll have to be more specific, but you might find the answers to such problems as inserting rows, using OFFSET with formulas. And you will probably find why extending formulas and does not work for you. -- if any of those are the problem. http://www.mvps.org/dmcritchie/excel/insrtrow.htm I don't know what you mean by locking the sheet, have you turned on sheet protection or have merged cells in your copy.. --- HTH, David McRitchie, Microsoft...

copy cells which are horizontal to verticle
Hi, How do I copy a set of cells which are horizontal to verticle or vice versa? For example: A B C D E F G H I J 1 a 2 b 3 c 4 d 5 6 To become A B C D E F G H I J 1 a b c d 2 3 4 5 6 Thanks. You could copy the selection and then use paste special and check the transpose check box on the bottom right corner of the dialog box. Ed -- nuver ------------------------------------------------------------------------ nuver's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=10036 View this thread: http://www.excelforum.com/show...

Need Off-Line Address List Explanation
I am running Exchange 2003 SP1 on Windows 2003. I am using Office 2003. I am hosting email for two different groups in a single domain and I only want users to see people in their respective group. I only need one group to have an Offline Address List. I have removed rights to authenticated users from the Default Global Address List. I have used security groups to give access to the Default Global Address List (for support people). The Default Global Address List contains everyone.. I have created two Global Address List and filtered each one on a custom attribute. Each list shows t...

Spreadsheet lists
I use excel to generate purchase orders. I would like to save the spread sheets by our job number which is input into the same cell on each P.O.. Can I set up something that will save the P.O. by that cell location automatically? Yes, you can do this with VBA. I will be making a few assumptions here, and will outline the assumptions in this proposed solution. 1) Press Alt + F11, to open the Visual Basic Editor (VBE) 2) Press Ctrl + R, to open the Project Explorer (PE; if not already open) 3) Select your file in left (PE) 4) Select Insert (menu) | Module 5) Copy/Paste the below code in ...

how do i change order of columns in a list published to sharepoint
I'd like to change the order of the columns in a list published to sharepoint. If I unlink and then relink, Excel will go and add another ID column. ...

some DLs are not listed in the GAL
Some of the DLs are not visible suddenly when I am seeing the GAL, when I am sending mail to the SMTP address it says the email address does not exist however it does when I am using ADUC to see the DL, I am using W2K native mode and we have E2K, I have checked that these are not hidden, can someone please help ...

How do I add cells from different worksheets?
I am trying to add totals from 5 separte worksheets on the last worksheet which I am calling a summary page. Can anyone share with me how to do this? I tried the Sum button and then holding control as I click on each total on the different worksheets but it doesn't seem to be working. Assuming the amounts are on the same cell on each worksheet use a formula like this: =SUM(Sheet1:Sheet5!A1) Otherwise you'd need a formulas like this: =Sheet1!A1+Sheet2!B2+Sheet3!C3+.. -- Jim Rech Excel MVP "ExcelErin" <ExcelErin@discussions.microsoft.com> wrote in message news...

applying dropdown list entry to multple cells
Is there a way to get the entry from a dropdown list to apply to multple cells? All the selected cells have the same list set in their validation rules, but simply selecting many cells and picking from the list in one does not work. Select your cells, select the value on one drop down, then press F2 and Ctrl-Enter. HTH, Bernie MS Excel MVP "twild" <twild@discussions.microsoft.com> wrote in message news:5DCBC32F-624F-4D2E-B9DD-536BA723F453@microsoft.com... > Is there a way to get the entry from a dropdown list to apply to multple > cells? All the selected cells ha...

Condition based on multiple cells
I would like to look at 3 cells: A6, C6, E6. If all 3 of these cells are blank, I would like to return a blank cell. If any of the 3 cells has a value, I would like to return the average. I am familar with how to do this based on 1 cell, but confused as to how to get it to work with 3! =IF(AND(A6="",C6="",E6=""),"",AVERAGE(A6,C6,E6)) WAR wrote: > I would like to look at 3 cells: A6, C6, E6. If all 3 of these cells are > blank, I would like to return a blank cell. If any of the 3 cells has a > value, I would like to retur...

Drop Down List Filters
I've got a three tier dependent list set up for: Division / Group / Line. (Thanks Debra Dalgliesh) If a user selects a division, group and line there are no issues. However, after selecting all three fields, I've had some users go back and change the contents of the Division. When they do this - it makes the content of Group and Line not make sense. Is there a way to 'blank out' the group and line lists if the division list is entered/changed? or to force the user to re-enter group and line if division was changed. thanks in advance General Question - gen...

Global Address List Permissions HELP
Hi, How/where do I give a certain user permission to add to/create/edit the Global address book through outlook?? I've given myself (for testing purposes)full control of the Global Address List Security in Exchange but still am unable to create a new Distribution Group in the Global Address list... "You cannot create entries for this address book" Thanks for any help. ...

pick list
how do you input a drop down list i know how to hide the data You can use data validation to create a dropdown list in a cell. There are instructions in Excel's Help, and here: http://www.contextures.com/xlDataVal01.html monty wrote: > how do you input a drop down list > i know how to hide the data -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html ...

HOW CAN I PUT A TICK IN A CELL
Can anyone tell me how I can put a tick mark in a cell in Office Excel Font Marlett, letter a -- HTH RP (remove nothere from the email address if mailing direct) "Kevin" <Kevin@discussions.microsoft.com> wrote in message news:4EAEA117-A8B0-46D0-BBEB-1666282DC481@microsoft.com... > Can anyone tell me how I can put a tick mark in a cell in Office Excel Go to Insert and symbol and scroll down about two thirds, all symbols are there "Kevin" wrote: > Can anyone tell me how I can put a tick mark in a cell in Office Excel ...

getting msg: cannot sort merged cells not same size
Hi. someone sent me what looks like a run of the mill excel file . When I try to sort it I get a message saying the "operation requires merged cells to be identically sized". All the cells look the same size to me and as far as I know no merging was done to create the file.... I am attempting to contact the creator of the file but so far no luck. any ideas? Thank you. press Ctrl-A to select the entire sheet, then Format>Cells>Alignment and make sure the Merge Cells option is unchecked. "help with mas data" wrote: > Hi. someone sent me what looks like a ru...