Count cells in column B where criteria is met in column A cells.

[Don Black]

I have some data in cells in column "B", that I want to count. But I only want the cells to be counted when (coulmn A cells = 2014). I keep getting a value error I used this formula
=IF(A8:A713="2014",Count(B8:B713),""). I know something is not right.

 

[eibi]

=SUMPRODUCT((--(A8:A713=2014))*(--(B8:B713<>"")))

 

[Don Black]

Ok, Eibi what about for filtered items.

 

[Mike H..]

I have some data in cells in column "B", that I want to count. But I only want the cells to be counted when (coulmn A cells = 2014). I keep getting a value error I used this formula
=IF(A8:A713="2014",Count(B8:B713),""). I know something is not right.

Hi,

I effect all you're really trying to do is count 2014 in col A and you can use yhis

=COUNTIF(A8:A713,2014)

If you want to test for 2014 in Col A and only include Col B id there's something in it you can use this.

=SUMPRODUCT((A8:A713=2014)*(B8:B713<>""))

 

[eibi]

The formula I suggested will include hidden rows in the count.

Did you want to include or exclude hidden rows?

 

[Don Black]

exclude

 

[eibi]

If you want to exclude the hidden rows,

=SUMPRODUCT((--(A8:A713=2014))*(--(B8:B713<>""))*(SUBTOTAL(2,OFFSET(B8:B713,ROW(B8:B713)-MIN(ROW(B8:B713)),,1,1))))

I originally found this formula at

http://blog.excelgeek.com/2010/11/sumproduct-that-only-includes-visible.html

but subsequently did some research and found this application of OFFSET credited as early to Laurent Longre in 1999:

http://j-walk.com/ss/excel/eee/eee001.txt

If anybody knows why it works, I'm interested to learn...

 

[Mike H..]

exclude

Hi,

This will work for text or numeric data in col b

=SUMPRODUCT(SUBTOTAL(3,OFFSET(A8:A713,ROW(A8:A713)-MIN(ROW(A8:A713)),,1))*((A8:A713=2014)*(B8:B713<>"")))

 

[Hui]

Have a read of this post which offers an alternative solution:
http://chandoo.org/wp/2014/07/23/formula-forensics-no-037-how-to-count-and-sum-filtered-tables/