Summing based on text range?

[jpletting]

Disclaimer: I'm no expert at Excel by any means, so I don't know a lot of the functions.

I'm trying to do this:

User enters, in text, a range of receipt numbers on SS1. (A1 = "11234" and B1="11567")

Excel takes that range of numbers and does "for all receipts numbers within [entry in A1]-[entry in B1] on SS2, calculate the amount in column C" and returns a value to A2... without knowing the actual cell range of the receipts, because it changes daily.

Does this make sense? If so, can it be done? I think it can with the right combo of formulas, but I don't know them at all. Has anyone else tried to do something similar? I checked the archives but didn't see something like this. Thanks!

[NBVC]

Why text entered?

Are the numbers in SS2 also text entered?

If so,

=SUMPRODUCT(--(SS2!A1:A10+0>=A1+0),--(SS2!A1:A10+0<=B1+0),SS2!B1:B10)

where SS2!A1:B10 contain your datatable.

adjust ranges to suit.

[jpletting]

Text-entered because it's a reconciliation sheet for counting safe cash and it needs to be indicated which envelopes each person opened in order to balance back to the system report. SS2 is also text-entered, a copy of the report in Excel format and text-to-columned. What I'm trying to do is create a cell that shows how much cash is expected from each envelope range when the person counting indicates it on their recon sheet. It will make it much easier to figure out whose batch is off when we don't immediately balance.

Thanks for the help! I'll try it and let you know.

Edit: WOW!! It works perfectly... I knew it had to be something relatively simple. I was easily able to read your formula and felt like a fool for not getting it myself. :D