Find order number and count if between range

[sonata7245]

I have a spreadsheet setup that records mistakes on orders. In one column it has the order number of the mistake as it is logged on a second sheet I have the order ranges that have gone out. I need a forumla that will check the column that has the order number for the mistake order and see if it is between the order ranges on the other sheet then count how many mistakes there are.

Example:
Sheet1
A1
123456
123457
123458
123459
123460

Sheet 2
A1 B1
123450 123456
123457 123459

So the output from the formula would show 4

Any help you can give would be much appreciated. Also this is in google spreadsheets but I figure the formula should be pretty similar.

[FDibbins]

Thanks for the title change

Your profile says 2003, so use this, copied down...
=SUMPRODUCT(($A$4:$A$8>=A12)*($A$4:$A$8<=B12))

If you do have 2007 or later, use this simpler version...
=COUNTIFS($A$4:$A$8,">="&A12,$A$4:$A$8,"<="&B12)

[sonata7245]

I tired the sumproduct function and it gives me the following error: error: Range has no entry corresponding to this cell

I changed it just a little to fit what I am trying to do:
=SUMPRODUCT((Sheet1!$A$5:$A$5980>=$A$3:$A1000)*(Sheet1!$A$5:$A$5980<=$B$3:$B$1000))

I probably messed up somewhere. A little more info:
Sheet 1 column A has the order numbers that we log as errors are reported to us.
Sheet 2 has Column A and Column B
Column A is the beginning of the order range
Column B is the end of the order range

Hope that helps. Countifs does not work in google spreadsheets.

[FDibbins]

why are you using a range for the >= part?

From your sample, it looked like you wanted to count for each range?

[sonata7245]

There are multiple ranges being entered over time. If its not possible to have just one formula to do a total count I can input it manually then do a sum.

[martindwilson]

Countifs does not work in google spreadsheets.?????????? why you asking in Excel Formulas & Functions then?

[sonata7245]

I figured they were similar enough programs that there may be something close I can work with. I did get the sumproduct formula to work for a a specific order range but it returned a zero value.

[sonata7245]

Never mind its working now. Thank you so much!

[FDibbins]

Happy to help