A word to = a number

Simon - is this what you had in mind?

See attached spreadsheet.

Simon, in order to get an accurate response I suggest uploading a sample workbook with data that matches your actual data in layout and format (does not need to be actual data if that is confidential).

Show your "BEFORE", as well as how you would like to see the results AFTER any calculations or macros are used.

Something like this, perhaps?

which values?

I dont understand your requirement - pls give greater detail.

@Paul - thanks you are right. the more detail the better..........

@ simon. i haven't seen JB's offering but you can bet your bottom doller that it knocks the socks off mine, so go with him!!


All the best

I see, now... Try:

=C4-(COUNTIF(B9:B14,"<>Available"))

Hi JB,

I have had the chance to look at your solution. I think i'll save it to my "excel nougat" folder as may come in handy one day!

If you don't mind, there is part of the formula which I dont understand, I can imagine, but would like confirmation, should you have the time to help. =COUNTIF(A:A, F3&"*"), I have highlighted the bit in Bold U'line.

Could you also translate this one into English please, in particular the reason for the use of left and len. =SUMPRODUCT(--(LEFT($A$2:$A$20,LEN($F5))=$F5), --($B$2:$B$20=H$2))

Thanks for your anticipated help and patience.

Have a good day

Cheers

My example has items called:

VCR1
VCR2
VCR3
...etc,

Then in F2 is just the basic listing "VCR" with no number. The F2&"*" adds a wildcard to the VCR string and basically means, "count every instance in column A of any string starting with VCR."

==========

The SUMPRODUCT() basically needs to do the same thing. This function doesn't like whole column references, so I specified the range and this part of formula:

(LEFT($A$2:$A$20,LEN($F5))=$F5)

...basically means look at F5 to see how many characters it is (VCR is 3) and then count how many cells in the range start with the same string that is in F5.

JB, Thanks v much indeed. Kind of you.

All the best

Simon,

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.