Formula help...

gary555

New Member
Joined
Jul 19, 2006
Messages
11
Hi guys,
Let's get to the point:

ColumnA ColumnB
1.99 5
2.99 5
3.99 5
1.99
2.99 5
3.99 5
1.99 5
2.99 5
3.99 5

O.K. simple question: I have the price of 1.99 listed three times, but the quantity is only there two times. How can I write a formula to COUNT the number of instances where Column A is 1.99 AND where column B is greater than 0?

In this case the formula should return 2, since only two "records" have 1.99 AND a quantity greater than 0.

I tried using AND and countif, but can't get it to work...

Any help is appreciated!!

Thanks! :biggrin:
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

jimrward

Well-known Member
Joined
Feb 24, 2003
Messages
1,760
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
you can use the SUMPRODUCT function as follows

=SUMPRODUCT((A2:A10=1.99)*(B2:B10>0))

remember to keep the ranges the same size
 

Watch MrExcel Video

Forum statistics

Threads
1,109,543
Messages
5,529,456
Members
409,878
Latest member
DDhol
Top