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

[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.

 

[Bernard Liengme]

While COUNTIFS is nice, SUMPRODUCT can do the same

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

Do not try to use full column references other than in XL 2007
=SUMPRODUCT(--(H:H<AB1),--(LEFT(C:C)="6")) ' No No No

For more details on SUMPRODUCT
Bob Phillips
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
J.E McGimpsey
http://mcgimpsey.com/excel/formulae/doubleneg.html
best wishes

 

[T. Valko]

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))

 

[Shane Devenshire]

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.

 

[2seas]

That worked. Thank you.

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))

 

[T. Valko]

You're welcome. Thanks for the feedback!