Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Excel formula Question

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Here goes
    I have three columns of data I want to have the computer to tell me how many fans I have from a certain location.
    example: B 1 Fan
    B 1 Boot
    c 1 Fan
    B 1 Car
    B 1 Fan
    I can use a countif statement to tell that I have three fans but, I want something that will tell me how many fans do I have that are from the B location in column a. and then how many boot from the b location etc...
    Thanks

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Have you considered using a PivotTable?

  3. #3
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ~Anne Troy

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Where the wild roses grow
    Posts
    285
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Best thing to use is the sumproduct formula, works like this:

    =SUMPRODUCT((A:A="B")*(B:B="1")*(C:C="Fan"))

    This will look down the 3 columns. If there's a 'B' in column A with a '1' next to it in column B and a 'Fan' next to that in column C it'll return a 1 and do the same all the way down the page.

    Obviously you can tinker with this to make it count what you want

    Audiojoe
    I give love a bad name

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,619
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-03 07:46, tweetyrm wrote:
    Here goes
    I have three columns of data I want to have the computer to tell me how many fans I have from a certain location.
    example: B 1 Fan
    B 1 Boot
    c 1 Fan
    B 1 Car
    B 1 Fan
    I can use a countif statement to tell that I have three fans but, I want something that will tell me how many fans do I have that are from the B location in column a. and then how many boot from the b location etc...
    Thanks
    =SUMPRODUCT((A2:A6="B")*(C2:C6="Fan"))

    will for example give you the desired count.

    Try also using PivotTables.

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
  •