How can I count items in a filtered list?

[Steven j P]

T. Valko, Thanks, It did not work at first;

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B100,ROW(B2:B100)-ROW(B2),0,1)),--(LEFT(B2:B100,2="CL"))

Needed to add 1 ")" to make it work, see below, Thanks again

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B100,ROW(B2:B100)-ROW(B2),0,1)),--(LEFT(B2:B100,2)="CL"))

Steve

 

[T. Valko]

Needed to add 1 ")" to make it work

Yeah, that was my fault. <argh>

Thanks for the feedback!

 

[Antonella]

Hi I need your help..
I know that is not the right forum but I'll try anyway.
Hope you will understand what I’m trying to do.
I’ve got 2 worksheets called Total.xls Monthly.xls in a workbook called PM.
I’ve linked few cells from Monthly to Total (that’s was easy). Now all the
formula already in Total give Error as results.
Any help greatly appreciated.
Antonella

 

[Gord Dibben]

Your syntax is not correct.

You cannot have Total.xls and Monthly.xls in one workbook.

An *.xls is a single workbook.

Do you mean you have two worksheets named Total and Monthly in a workbook
named PM.xls

Is this latest problem you are posting in any way related to the original
posting about counting filtered items you and Biff have been working on?


Gord Dibben MS Excel MVP

 

[Antonella]

Yep I've got 2 worksheets linked to each other. How can I make a formula
works if the cells I need to calculate are linked to the first worksheet.
Hope do you get my point?

 

[Gord Dibben]

Again I ask............are your calculations concerning what you and Biff
were discussing?

I have not follwed the thread so not sure what you're asking for.

But you can calculate using linked cells.

Say sheet1 has these linked cells in A1:A3

=Sheet2!G1
=Sheet2!H2
=Sheet2!D1

Enter =SUM(A1:A3) in any cell on sheet1

If not clear to you, post some examples of linked cells and what type of
calculating you need done.


Gord

 

[Dominic_gates]

Hi,

I wonder if someone can help me,

I am trying to count the number of individuals in two seperate columns on
the same spreadsheet based on values that are text and numbers, i.e. "in
column B1:B391 which are 'Con'" and "in column K1:K391 which have a value of
'1'".

i have tried the formula suggested below:

=SUMPRODUCT(--(A1:A100="Netherlands"),--(B1:B10="July"))

adapting this to:

=SUMPRODUCT(--(B1:B391="Con"),--(K1:K391="1"))

however this does not work.

Any help would be amazing

Many Thanks

Dominic

 

[Domenic]

Try removing the quotes from the second criteria...

=SUMPRODUCT(--(B1:B391="Con"),--(K1:K391=1))

 

[Dominic_gates]

Amazing! it works thank you!!!

Many Thanks

Dominic

 

[R. Skowron]

This will "COUNTIF" B2:B100 = "A" in a filtered list:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B100,ROW(B2:B100)-ROW(B2),0,1)),--(B2:B100="A"))

--
Biff
Microsoft Excel MVP



The use of the "sumproduct" formula confuses me and how it's applied.

I have a report at work that lists a number of categories for multiple
people. With the use of the "countif" formula I'm able to identify the
quantity associated with each category for the entire group. However, I want
to filter down to a particular individual and have the quantities now only
apply to that individual. Is there a way to combine the "countif" and
"subtotal" formulas to make this happen?

 

[A Shuttle]

Found this through Excel help - exactly what I wanted, and in less than 5
mins too

Thanks very much

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Found this through Excel help - exactly what I wanted, and in less than 5
mins too

Thanks very much

 

[Dan W]

It works for me, too! Thanks very much for your help!

Also one quick note for other people trying to count TRUE or FALSE values, I
had to remove the double quotes - apparently Excel see's them as special
values, even though it doesn't seem to convert them to numbers (the way a
database does).
This worked for counting the number of TRUE's in a filtered list
=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B100,ROW(B2:B100)-ROW(B2),0,1)),--(B2:B100=TRUE))

 

[T. Valko]

trying to count TRUE or FALSE values,

I had to remove the double quotes -
apparently Excel see's them as special values

TRUE and FALSE are special values in Excel. They're called Boolean values.
In Excel a Boolean TRUE and FALSE is different from a *text* entry of true
or false. If no special formatting has been applies a Boolean will appear in
the cell as all uppercase characters and centered in the cell.

 

[David]

Hi.
I was wondering if you could explain the occurence of TWO adjacent minus
signs part way through this formula.
Thanks,
David

 

[T. Valko]

--(B2:B100="A")

This expression will return an array of either TRUE or FALSE:

(B2:B100="A")

B2: X
B3: A
B4: A
B5: C

B2="A" = FALSE
B3="A" = TRUE
B4="A" = TRUE
B5="A" = FALSE

SUMPRODUCT calculates numbers so we have to convert those logical TRUE and
FALSE to numbers. The TWO adjacent minus signs, known as double unary, is
one way to do that.

--TRUE = 1
--FALSE = 0

--(B2="A") = 0
--(B3="A") = 1
--(B4="A") = 1
--(B5="A") = 0

So we end up with an array of 1s and 0s:

{0;1;1;0}

The result of the SUBTOTAL function is also an array of 1s and 0s. For
example: {0;1;1;1}.

These 2 arrays are then multiplied together to arrive at the final result of
the formula:

Subtotal......B2:B5="A"
{0;1;1;1}*{0;1;1;0}

0*0 = 0
1*1 = 1
1*1 = 1
1*0 = 0

SUMPRODUCT({0;1;1;0}) = 2

See this for more info:

http://xldynamic.com/source/xld.SUMPRODUCT.html

 

[C Smith]

This works (great); however, it seem to work only for non-numerical data. I
am analyzing a survey the numerical responses where 5 = strongly agree, 4
=agree, etc. I really don't want to convert it all to alpha-characters since
I am performing other statistical functions on those cells. Any ideas?

 

[T. Valko]

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B100,ROW(B2:B100)-ROW(B2),0,1)),--(B2:B100="A"))

it seem to work only for non-numerical data.

Just a couple of minor changes should do the trick:

=SUMPRODUCT(SUBTOTAL(2,OFFSET(B2:B100,ROW(B2:B100)-ROW(B2),0,1)),--(B2:B100=n))

Where n = the number to count. For example, to count the number of times 5
appears in the filtered (or unfiltered) range:

=SUMPRODUCT(SUBTOTAL(2,OFFSET(B2:B100,ROW(B2:B100)-ROW(B2),0,1)),--(B2:B100=5))

 

[kosageinusha18]

Hi just a noob here,

I would like to know how or what function do I need to use on this kind of
problem. I am creating a template where the 2nd column B3:B50 has a drop down
option, so I'd like that Column to be counted examples on the drop down are .
NET - TNBA Callbacks and NRA - Montreal EMT.

Ex. if B2:B50 have (10) . NET - TNBA Callbacks it will appear (10) on J15
and if NRA - Montreal EMT have (3) it must appear (3) on J16.

hope that helps in explaining...its too hard to explain, I am wishing if I
could attach the file for better view..

thanks again.. waiting for your response

 

[T. Valko]

the 2nd column B3:B50 has a drop down option

Assuming that means you have AutoFilter applied...

Try these:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B3:B50,ROW(B3:B50)-ROW(B3),0,1)),--(B3:B50="NET
- TNBA Callbacks"))

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B3:B50,ROW(B3:B50)-ROW(B3),0,1)),--(B3:B50="NRA
- Montreal EMT"))