Excel Formula

[pa2]

I need an Excel formula to perform the following: -

row columnA columnB columnC columnD
1 243EB5012 243EB5001 243EB6000 2
2 69XY6018 69XY6001 69XY7000 1
3 243EB5432 69XY7001 69XY8000 3
4 83TU6574
5 69XY7025
6 69XY7885
7 69XY7376

Count totals for columnA where the values in columnA fall between the values in columnB and columnC.

Result should be columnD.

 

[xlbo]

need more info. I assume from the data that by "between" you mean "having the same identifier at the front but having the last 4 digits between the last 4 digits on the reference data"

Question: Are the identifiers at the front always either 4 or 5 characters?
Are the number on the right hand side always 4 digits (ie from 1000 to 9999) ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[pa2]

The formula would return a total count for all values in columnA by group as they fall in the range of values between columnB and columnC for each row ie row1, row2 etc.
Fron identifiers are always 4 or 5 characters followed by 4 digit number ie 1001-2000

 

[xlbo]

given those criteria, this should work for you:

=SUMPRODUCT((LEFT($A$1:$A$100,LEN($A$1:$A$100)-4)=LEFT($B1,LEN($B1)-4))*(VALUE(RIGHT($A$1:$A$100,4))>=VALUE(RIGHT($B1,4)))*(VALUE(RIGHT($A$1:$A$100,4))<=VALUE(RIGHT($C1,4))))

Where your data is in A1:A100 and your test values are in columns B & starting in row 1

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[yogia]

Hi paz1:

Let me see if I understood you correctly ...

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[xlbo]

The above post has been red flagged as it has nothing to do with the thread subject. Please post in a new thread

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[xlbo]

By that, I don't mean the one from Yogia ;-)

Yogia - how does your formula compensate for different lengths of reference cell - some have a 5 char start before a 4 char number whereas others have only 4 chars 1st

In the 3rd example, lets say that the 2 references were

69XY7001 69XY9000

I don't think your formula would count 69XY8001 as being included....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[yogia]

Hi Geoff:

Good Call! Even though the formulation I posted worked for the OP's posted data, it could give a wrong result under the scenario you posted. So I have modified my formulation to vary the number of characters to be compared in the LEFT comparison ...

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[yogia]

And another way using the DCOUNTA function ...

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com