Results 1 to 7 of 7
  1. #1
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    count in two columns (2K3)

    Sorry, I have searched but cannot think of the right terminology.

    What is the formula if I want to count for the word Rushtent in column E and that there is a number 1 in column G, I have tried sum and sumproduct.
    Jerry

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: count in two columns (2K3)

    "Normal" formula:

    =SUMPRODUCT((E1:E1000="Rushtent")*(G1:G1000=1))

    Array formula (confirm with Ctrl+Shift+Enter):

    =SUM((E1:E1000="Rushtent")*(G1:G1000=1))

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: count in two columns (2K3)

    Thanks Hans

    You know what the irritating thing is, I did this formula and it didn't work for me, I have just found that after using your formula and it still didn't work[gggrrrr]

    Double checking I found the reason was due to my SQL extract having padding, so a trim did the job. Thanks for your help again.
    Jerry

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: count in two columns (2K3)

    I have been playing with this formula and the one below works fine but I was wondering is there a way to shorten it.

    I am looking for everything in range C1:3955 that does not equal 104 or 102491 or 102492


    =SUMPRODUCT((Format!F$1:F$3955=E14)*(Format!C$1:Fo rmat!C$3955<>102491)*(Format!C$1:Format!C$3955<>10 2492)*(Format!C$1:Format!C$3955<>104))
    Jerry

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: count in two columns (2K3)

    You can certainly do it if you put those values into a range of cells (say J1:J3):
    <code>=SUMPRODUCT((Format!F$1:F$3955=E14)*(COUNTIF ($J$1:$J$3,Format!$C$1:$C$3955)=0))</code>
    should work.
    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: count in two columns (2K3)

    I don't know if it's really simpler, but you could put the values you want to exclude in a range of cells, say P1:P3, and use this formula:

    =SUMPRODUCT((Format!F$1:F$3955=E14)*ISERROR(MATCH( Format!C$1:C$3955,P1:P3,0)))

    If you name the range of exclusions Excluded (it can be a dynamic range, for more flexibility), the formula becomes

    =SUMPRODUCT((Format!F$1:F$3955=E14)*ISERROR(MATCH( Format!C$1:C$3955,Excluded,0)))

  7. #7
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: count in two columns (2K3)

    Hi Rory

    I think this one will get my vote ( for this particular application) as I will be handing it over to an Excel savvy user who is using this to count Performance Indicators and they can adapt the range to fit their needs.

    Hans, yours is great, thank you.
    Jerry

Posting Permissions

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