Excel functions

We may earn a small commission from affiliate links and paid advertisements. Terms

C

corvetteguy

Senior Member
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
 
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))))
 
Originally posted by pissedoffsol@Aug 10 2004, 10:55 PM
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))))
[post=375109]Quoted post[/post]​
Click to expand...



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 <_<
 
Originally posted by pissedoffsol@Aug 10 2004, 10:55 PM
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))))
[post=375109]Quoted post[/post]​
Click to expand...


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
 
Pivot tables are a great resource to my friend...
 
sort and refresh the data instantly.

Counts the number of instances, sums, etc. Pivot Tables are very useful, especially with this type of scenario...
 
You must log in or register to reply here.
Back
Top