Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...
MS Office Forum / Excel / Worksheet Functions / March 2008

Tip: Looking for answers? Try searching our database.

count function

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
AJ Patel - 11 Mar 2008 00:45 GMT
I am trying to come up with a formula that will look at a column for a word
or set of words, look at a 2nd column and give me a running total in
another column as long as none of the values in the 2nd column match.  For
example, use the following worksheet
        A        B         C           D
1    Ball       1                 Ball        2
2    Bat       2                  Bat        1
3    Ball       3                 Glove     1
4    Ball       3
5    Glove    4
for Cell D1 I want the function to look at column A for Ball give me
a running total as long as the numbers in column B do not match, so in this
case you can see that cell B3 and B4 both have 3, so I only need that counted
once for
cell d1 therefore my running total would be 2.  Also how can I look at
column A and look for two sets of words and give me a running total. For
example the same scenario if i want to look for "bat" and "ball" in column A
then look at column B make sure there is nothing that matches and then give
me a running total, in this case total would be 3. I hope I explained what I
need clearly
Reply to this Message
RagDyer - 11 Mar 2008 02:37 GMT
Changing your scenario slightly, let's say that you enter the word(s) to
count in Columns C and D, and we display the totals in Column E using this
*array* formula in E1:

=COUNT(1/FREQUENCY(IF(($A$1:$A$5=C1)+($A$1:$A$5=D1),MATCH($B$1:$B$5,$B$1:$B$5,0)+2),ROW($1:$5)))

Signature

Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of
the regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.  Also, you must use CSE when
revising the formula.

*After* the CSE entry, copy the formula down as needed.

If you're only looking to count a single word, simply leave either Column C
or D blank.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

>I am trying to come up with a formula that will look at a column for a word
> or set of words, look at a 2nd column and give me a running total in
[quoted text clipped - 21 lines]
> I
> need clearly
Reply to this Message
T. Valko - 11 Mar 2008 04:32 GMT
A few keystrokes shorter:

For multiple crteria:

=COUNT(1/FREQUENCY(IF((A1:A5=C1)+(A1:A5=D1),B1:B5),B1:B5))

If there might be empty cells in B1:B5 those will be counted as 0. To
account for that if needed:

=COUNT(1/FREQUENCY(IF(((A1:A5=C1)+(A1:A5=D1))*(B1:B5<>""),B1:B5),B1:B5))

For a single criteria:

=COUNT(1/FREQUENCY(IF((A1:A5=C1,B1:B5),B1:B5))

=COUNT(1/FREQUENCY(IF((A1:A5=C1)*(B1:B5<>""),B1:B5),B1:B5))

All formulas array entered.

Signature

Biff
Microsoft Excel MVP

> Changing your scenario slightly, let's say that you enter the word(s) to
> count in Columns C and D, and we display the totals in Column E using this
[quoted text clipped - 29 lines]
>> what I
>> need clearly
Reply to this Message
RagDyeR - 11 Mar 2008 17:24 GMT
Yep! .. a little shorter.<bg>
Signature


Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

A few keystrokes shorter:

For multiple crteria:

=COUNT(1/FREQUENCY(IF((A1:A5=C1)+(A1:A5=D1),B1:B5),B1:B5))

If there might be empty cells in B1:B5 those will be counted as 0. To
account for that if needed:

=COUNT(1/FREQUENCY(IF(((A1:A5=C1)+(A1:A5=D1))*(B1:B5<>""),B1:B5),B1:B5))

For a single criteria:

=COUNT(1/FREQUENCY(IF((A1:A5=C1,B1:B5),B1:B5))

=COUNT(1/FREQUENCY(IF((A1:A5=C1)*(B1:B5<>""),B1:B5),B1:B5))

All formulas array entered.

Signature

Biff
Microsoft Excel MVP

> Changing your scenario slightly, let's say that you enter the word(s) to
> count in Columns C and D, and we display the totals in Column E using this
[quoted text clipped - 29 lines]
>> what I
>> need clearly
Reply to this Message
AJ Patel - 11 Mar 2008 18:10 GMT
Thanks, now if I want to take that a little further, using the following
worksheet
         A          B               C         D
1      Bat       Defective       1
2      Bat       Defective       1
3      Ball       Defective       2
4      Bat       Defective       3
5      Bat       Good             4
n      Glove    Defective       5

I want a running total of bat, ball, glove, using those values as a search
string, that are defective from column B, and the value in C does not match.

> A few keystrokes shorter:
>
[quoted text clipped - 48 lines]
> >> what I
> >> need clearly
Reply to this Message
RagDyer - 11 Mar 2008 20:53 GMT
Building of Biff's shorter version, using D1, E1, and F1 as cells to contain
the search words, try this *array* formula in G1:

=COUNT(1/FREQUENCY(IF(((A1:A6=D1)+(A1:A6=E1)+(A1:A6=F1))*(B1:B6="Defective"),C1:C6),C1:C6))

Signature

Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of
the regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.  Also, you must use CSE when
revising the formula.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

> Thanks, now if I want to take that a little further, using the following
> worksheet
[quoted text clipped - 65 lines]
>> >> what I
>> >> need clearly
Reply to this Message
T. Valko - 11 Mar 2008 20:59 GMT
See your other post

Signature

Biff
Microsoft Excel MVP

> Thanks, now if I want to take that a little further, using the following
> worksheet
[quoted text clipped - 65 lines]
>> >> what I
>> >> need clearly
Reply to this Message
Teethless mama - 11 Mar 2008 03:31 GMT
In D1:
=SUM(N(FREQUENCY(IF($A$1:$A$5=C1,MATCH($B$1:$B$5,$B$1:$B$5,0)),MATCH($B$1:$B$5,$B$1:$B$5,0))>0))

ctrl+shift+enter, not just enter
copy down

> I am trying to come up with a formula that will look at a column for a word
> or set of words, look at a 2nd column and give me a running total in
[quoted text clipped - 16 lines]
> me a running total, in this case total would be 3. I hope I explained what I
> need clearly
Reply to this Message
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage
Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.