How do I count the number of items that meet 2 criteria?

  • Thread starter Thread starter 2seas
  • Start date Start date
2

2seas

What I'm trying to do is count how many invoice numbers starting with 6 where
completed by a certain date. I've got a column with the date it was
completed and a column of invoice numbers.

Here's what I have so far:
for the date =COUNTIF(H6:H1000,"<"&AB1)
for the invoice numbers =COUNTIF(C6:C1000,"6*")

I just can't get it where it counts them if the row matchs both.

I'm useing Excel 2003 and I'm not allowed to upgrade it, otherwise I would
use the COUNTIFS function in Excel 2007.
 
Try this:

=SUMPRODUCT(--(LEFT(C6:C1000)="6"),--(H6:H1000<AB1))

Note that the LEFT function retruns a TEXT value. If you use a cell to hold
the number criteria 6 then you'd have to coerce it to evaluate as a TEXT
value.

AA1 = 6
AB1 = some date

=SUMPRODUCT(--(LEFT(C6:C1000)=AA1&""),--(H6:H1000<AB1))
 
Hi,

And you are correct, in 2007 you could do that with

=COUNTIFS(H6:H1000,"<"&AB1,C6:C1000,"6*")

provided the invoice numbers were entered as text.

It all the invoice numbers were entered as numbers and they all had the same
number of digits you could use (in 2007):

=COUNTIFS(B2:B5,">399999",B2:B5,"<500000")

However, if those coditions were not met you might still need to use the
SUMPRODUCT function shown in your earlier responses.
 
That worked. Thank you.

T. Valko said:
Try this:

=SUMPRODUCT(--(LEFT(C6:C1000)="6"),--(H6:H1000<AB1))

Note that the LEFT function retruns a TEXT value. If you use a cell to hold
the number criteria 6 then you'd have to coerce it to evaluate as a TEXT
value.

AA1 = 6
AB1 = some date

=SUMPRODUCT(--(LEFT(C6:C1000)=AA1&""),--(H6:H1000<AB1))
Click to expand...
 
Back
Top