# Find order number and count if between range

1. ## Find order number and count if between range

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.

2. ## Re: Find order number and count if between range

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)

3. ## Re: Find order number and count if between range

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

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

4. ## Re: Find order number and count if between range

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

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

5. ## Re: Find order number and count if between range

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.

7. ## Re: Find order number and count if between range

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.

8. ## Re: Find order number and count if between range

Never mind its working now. Thank you so much!

9. ## Re: Find order number and count if between range

Happy to help

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1