Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Formula Question

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    What is the formula for wanting to get the TOTAL number of cells with the word "SGT" in the range A1:A50, BUT ONLY count them IF the word "GO" appears in the range B1:B50? I can get them separatly but not together. Please help.

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Florida
    Posts
    82
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    try the following

    =sum((a1:a65336="sgt"(*(b1:b65336="go"))

    Press Ctrl+shift+enter to make the formula work.........

  3. #3
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-31 17:52, kinkyparamour wrote:
    try the following

    =sum((a1:a65336="sgt"(*(b1:b65336="go"))

    Press Ctrl+shift+enter to make the formula work.........
    I think that should be

    =sum((a1:a65336="sgt")*(b1:b65336="go"))






    Kind Regards,
    Ivan F Moala From the City of Sails

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Florida
    Posts
    82
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Your right. Thanks for correcting my typeo...

    Denny

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Also, you may want to trim down those ranges in the formula to your actual A1:A50 - you'll notice the performance difference when you recalc.

    If your real data is, in fact, thousands of rows though, I'd suggest you take a look at Excel's database functions : =DCOUNT in particular.

    A couple of extra options


    :: Pharma Z - Family drugstore ::

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,034
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    On 2002-04-01 02:59, Chris Davison wrote:
    Also, you may want to trim down those ranges in the formula to your actual A1:A50 - you'll notice the performance difference when you recalc.

    If your real data is, in fact, thousands of rows though, I'd suggest you take a look at Excel's database functions : =DCOUNT in particular.

    A couple of extra options [img]/board/images/smiles/icon_smile.gif[/img]
    Chris,

    You amaze me. You need to make a distinction between a single cell array or SUMPRODUCT formula and one that gets copied down to a huge number of cells.

    Lets take the Roadrunner case.

    =SUM((A1:A50="SGT")*(B1:B50="GO"))

    array-entered, or

    =SUMPRODUCT((A1:A50="SGT")*(B1:B50="GO"))

    normally entered will not differ much in performance cost compared with DCOUNTA, not DCOUNT.

    In order to apply DCOUNTA, you must have labels in A and B, say, Field1 and Field2.

    You'll need to enter the following in an area, say, C1:D2.

    {"Field1","Field2";
    "SGT","GO"}

    Now, you can use:

    =DCOUNTA(A1:B50,1,C1:D2)

    Aladin


Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •