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.

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"2seas" wrote in message
...
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))

--
Biff
Microsoft Excel MVP


"2seas" wrote in message
...
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.

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.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"2seas" wrote:

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.

That worked. Thank you.

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"2seas" wrote in message
...
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.

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"2seas" wrote in message
...
That worked. Thank you.

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"2seas" wrote in message
...
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.