Count Formulas

**Martin9** · 08-03-2012, 04:39 AM

Hi, I have a list of data with a reference in column A, some rows have a date in column D, I am trying to type a formula in that picks up how many lines of data (for a paticular refernce have a date in column d?) Does anybody know what the formula is?

For example one of the references in column a is "HD" there are currently over 800 lines of data with that reference, some of those will have a date in column D I just need a quick and easy way of calculating how many, without having to filter etc.
Regards

**Ace_XL** · 08-03-2012, 04:42 AM

Try..

=COUNTIFS($A$1:$A$100,A1,$D$1:$D$100,">"&1/1/1900)

**benishiryo** · 08-03-2012, 05:05 AM

hi Martin9. since you are also using Excel 2003, you may want to try:
=SUMPRODUCT((A1:A10="HD")*(D1:D10>1))

that is assuming your data is from row 1:10.

**Martin9** · 08-03-2012, 07:19 AM

Originally Posted by benishiryo

hi Martin9. since you are also using Excel 2003, you may want to try:
=SUMPRODUCT((A1:A10="HD")*(D1:D10>1))

that is assuming your data is from row 1:10.

This works perfect for what I need thanks