Subtotals without the use of pivots or groups

[Chris C]

I´m using the following array formulae which counts the
number of occurences of text or numbers in a column.My
problem is that I would like this to be conditonal to a
specified criterea (in this case "peoples names"). Is this
posible without the use of Pivot Tables?

=SUM(IF(FREQUENCY(MATCH(B1:B13,B1:B13,0),MATCH
(B1:B13,B1:B13,0))>0,1)) gives a result of 3. I´m looking
for a formulae that was conditional to "arthur" to give a
result of 2

arthur a
arthur a
arthur a
arthur a
arthur a
arthur a
arthur b
arthur b
arthur b
trevor c
trevor c
trevor c
trevor c

 

[Leo Heuser]

Chris

With the name (Arthur) in C1
this array formula will do the job:

=SUM((FREQUENCY(IF((A1:A13=C1),MATCH(B1:B13,B1:B13,0)),
IF((A1:A13=C1),MATCH(B1:B13,B1:B13,0)))>0)+0)

To be entered with <Shift><Ctrl><Enter>, also if
edited later.

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Chris C" <[email protected]> skrev i en meddelelse
I´m using the following array formulae which counts the
number of occurences of text or numbers in a column.My
problem is that I would like this to be conditonal to a
specified criterea (in this case "peoples names"). Is this
posible without the use of Pivot Tables?

=SUM(IF(FREQUENCY(MATCH(B1:B13,B1:B13,0),MATCH
(B1:B13,B1:B13,0))>0,1)) gives a result of 3. I´m looking
for a formulae that was conditional to "arthur" to give a
result of 2

arthur a
arthur a
arthur a
arthur a
arthur a
arthur a
arthur b
arthur b
arthur b
trevor c
trevor c
trevor c
trevor c

 

[Bernard Liengme]

I think we need more. Is "arthur a" in one or two cells?
You want to know that 'arthur' occurs with two different values in adjacent
cells?
Can "trevor" have accompanying values of "a" and "b" or only "c"?

--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in e-mail address


I´m using the following array formulae which counts the
number of occurences of text or numbers in a column.My
problem is that I would like this to be conditonal to a
specified criterea (in this case "peoples names"). Is this
posible without the use of Pivot Tables?

=SUM(IF(FREQUENCY(MATCH(B1:B13,B1:B13,0),MATCH
(B1:B13,B1:B13,0))>0,1)) gives a result of 3. I´m looking
for a formulae that was conditional to "arthur" to give a
result of 2

arthur a
arthur a
arthur a
arthur a
arthur a
arthur a
arthur b
arthur b
arthur b
trevor c
trevor c
trevor c
trevor c

 

[Chris C]

Leo,
Thanks, I tested it out and arrived at #value error?
Please can you help.

Chris

 

[Chris C]

Hi Bernard,
"Arthur a" is in adjacent cells. A1 & B1. Yes, I would
like to know how many different values Arthur occurs with.
Trevor would have `differing` unique values.eg. e, f, g

Chris

 

[Leo Heuser]

I have mailed my testbook to you.

LeoH

 

[Aladin Akyurek]

With the morefunc.xll add-in (http://longre.free.fr/english)...

=COUNTDIFF(SETV(IF((A1:A13=C2)*(B1:B13<>""),B1:B13)))-ISNUMBER(MATCH(FALSE,G
ETV(),0))

which must be confirmed with control+shift+enter instead of just with enter.

This formula would withstand any empty cell in ranges in A and/or B.

C2 houses a name interest like "Arthur".

I´m using the following array formulae which counts the
number of occurences of text or numbers in a column.My
problem is that I would like this to be conditonal to a
specified criterea (in this case "peoples names"). Is this
posible without the use of Pivot Tables?

=SUM(IF(FREQUENCY(MATCH(B1:B13,B1:B13,0),MATCH
(B1:B13,B1:B13,0))>0,1)) gives a result of 3. I´m looking
for a formulae that was conditional to "arthur" to give a
result of 2

arthur a
arthur a
arthur a
arthur a
arthur a
arthur a
arthur b
arthur b
arthur b
trevor c
trevor c
trevor c
trevor c