1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Excel functions

Discussion in 'Members' Lounge' started by corvetteguy, Aug 10, 2004.

  1. corvetteguy

    corvetteguy Senior Member

    Messages:
    888
    Likes Received:
    0
    Joined:
    Sep 29, 2002
    Location:
    Bristol ,CT
    Ok here's my dilema....I have a list ...My problem is i need to count all apples i have(not the sum of the amount of apples) Just how many times the word apples appears in colum A, then i want to see all apples that i have a quantity greater then 0...my though was =countif(b1:b20, ">0")-countif(a1:a20, apples) but this formula gives me the number 18(which would be all the numbers greater then 0, not bothering to check for the word apples in column A) Anyone have any ideas..I know i could do a FILTER and look at only apples in Column A. and change the 1st COUNTIF to B1:b8 and it will give me the right answer but that is a pain if you are constantly updating a list and there are over 2000 rows...Let me know if anyone has any ideas

    A B
    apples -12
    apples 3
    apples 0
    apples 45
    apples 34
    apples 12
    apples 23
    apples 67
    bananas 25
    bananas 156
    bananas 12
    bananas 34
    oranges 14
    oranges 5
    oranges 23
    oranges 32
    peaches 16
    peaches 5
    peaches 22
    peaches 23
     
  2. pissedoffsol

    pissedoffsol RETIRED

    Messages:
    49,693
    Likes Received:
    54
    Joined:
    Sep 28, 2002
    Location:
    Retirement Home
    this doesn't work for the 2nd arguement, but its sorta what you need to do

    =IF((A2="apples"),IF((B2>0),1,IF((A2="pears"),1,IF((B2>0),1,0))))
     
  3. tab

    tab Super Moderator

    Messages:
    3,839
    Likes Received:
    46
    Joined:
    Jan 10, 2003
    Location:
    Aberdeen, Washington State
    whoa, I thought I knew some nerd speak. :ph34r:
     
  4. dirt817

    dirt817 Member

    Messages:
    63
    Likes Received:
    0
    Joined:
    Jan 7, 2004
    Location:
    Pittsburgh



    I spent an entire year in high school in a class just for excel...and because my school was so awesome, we never got into anything like that <_<
     
  5. corvetteguy

    corvetteguy Senior Member

    Messages:
    888
    Likes Received:
    0
    Joined:
    Sep 29, 2002
    Location:
    Bristol ,CT


    Nah thats not gonna work at all, I need it to take in consideration to check for apples in Column A and that Column B is greater then 0 and to display the total of apples > 0
     
  6. corvetteguy

    corvetteguy Senior Member

    Messages:
    888
    Likes Received:
    0
    Joined:
    Sep 29, 2002
    Location:
    Bristol ,CT
    wooot, Problem solved =SUMPRODUCT(--(A1:A20="apples"),--(B1:B20 > 0))
     
  7. Bob Vila

    Bob Vila ɐןıʌ qoq Admin VIP

    Messages:
    4,670
    Likes Received:
    57
    Joined:
    Jul 4, 2003
    Location:
    Bristol, Ct.
    Pivot tables are a great resource to my friend...
     
  8. pissedoffsol

    pissedoffsol RETIRED

    Messages:
    49,693
    Likes Received:
    54
    Joined:
    Sep 28, 2002
    Location:
    Retirement Home
    wtf woudl a pivot tabel do for this formula? lol
     
  9. Bob Vila

    Bob Vila ɐןıʌ qoq Admin VIP

    Messages:
    4,670
    Likes Received:
    57
    Joined:
    Jul 4, 2003
    Location:
    Bristol, Ct.
    sort and refresh the data instantly.

    Counts the number of instances, sums, etc. Pivot Tables are very useful, especially with this type of scenario...
     
Verification:
Draft saved Draft deleted

Share This Page