#1
|
|||
|
|||
Count only numbers in filtered column
I have a problem. I have a worksheet with a in column A names, in column B team numbers and in the columns behind those names I have B's, M's, numbers and blanks. I want to be able to filter on the teams column and then count the amount of numbers in a certain column. So far I built this formula:
=(SUMPRODUCT(SUBTOTAL(3,OFFSET(GY10:GY1048576,ROW( GY10:GY1048576)-MIN(ROW(GY10:GY1048576)),,1)),--(GY10:GY1048576>1))) But it sees the B's and the M's as a value greater than 1 aswell How do I fix this? I hope someone can help me! |
#2
|
||||
|
||||
Try:
=SUMPRODUCT(SUBTOTAL(3,OFFSET(GY10:GY1048576,ROW(G Y10:GY1048576)-MIN(ROW(GY10:GY1048576)),,1)),(GY10:GY1048576>1)*I SNUMBER((GY10:GY1048576))) |
#3
|
|||
|
|||
Thank you for your quick reply!
It worked! (after I found out I had to remove 2 spaces because I got #NAME?) Thank you very very much! |
#4
|
||||
|
||||
ooops.... that's a forum glitch that added the space in "ISNUMBER". Sorry..
|
#5
|
|||
|
|||
Doesn't matter, I figured it out
One more question: What would the formula be if I wanted to count the B's and M's in the same column with filters? |
#6
|
||||
|
||||
Try:
Code:
=SUMPRODUCT(SUBTOTAL(3,OFFSET(GY10:GY1048576,ROW(GY10:GY1048576) -MIN(ROW(GY10:GY1048576)),,1)),--((GY10:GY1048576="M")+(GY10:GY1048576="B"))) |
#7
|
|||
|
|||
Thank you! Much easier to fix than I was thinking haha
|
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Count # of times "text" is in 1 column is specific date is in the other column | ann.acornacchio | Excel | 4 | 12-31-2016 05:40 PM |
Sum the (filtered) remaining work column | brentcfocus | Project | 3 | 03-21-2016 08:34 PM |
choose numbers from a set that must count up to 124 | adragner | Excel | 1 | 12-30-2013 09:51 AM |
count w and write into another column in excel | tomlam | Excel | 5 | 10-07-2012 06:09 AM |
Character count without numbers | student | Word | 1 | 05-10-2012 01:10 AM |