# Excel functions

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

1. ### corvetteguySenior Member

Messages:
888
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. ### pissedoffsolRETIRED

Messages:
49,693
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. ### tabSuper Moderator

Messages:
3,839
47
Joined:
Jan 10, 2003
Location:
Aberdeen, Washington State
whoa, I thought I knew some nerd speak.

4. ### dirt817Member

Messages:
63
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. ### corvetteguySenior Member

Messages:
888
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. ### corvetteguySenior Member

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

Messages:
4,673
61
Joined:
Jul 4, 2003
Location:
Bristol, Ct.
Pivot tables are a great resource to my friend...

8. ### pissedoffsolRETIRED

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

Messages:
4,673