Vlookup & Countif help

[sumonrezadu]

Plz see the attachment. Can it be possible to get what I want.
Following link is for ready reference for those who earlier solved my prob like that before.
http://www.excelforum.com/excel-gene...umn-index.html

[teylyn]

Please describe the nature of your issue in the narrative of the post, not just in the attachment. This will make it easier for answerers as well as solution seekers to evaluate the contents of the thread.

[JBeaucaire]

If you put this in C7 and copy down and across, the values appear to match your current sample values.

=SUMPRODUCT(--(LEFT($B$28:$B$48,6)=LEFT($B7,6)),--($C$28:$C$48=C$6))

Will that work for you?

[sumonrezadu]

Thx JBeaucaire. My prob is almost done but following prob still facing
1. If B28 changes with LP 407 MINIBUS, D7 doesn't become Zero and D8 doesn't become 2. But look my total qty in F7 & F8 really changes.
2. How can this formula recognize model similarity depicted in I19:I20
3. My todays report may goes down beyond B28:C48 which I collect from Branch Office and paste here

[JBeaucaire]

1) D7 shouldn't change when you change B28 to something else because C28 is "cash" so changing B28 will only change a value in column C above, the "cash" column.

2) The formula is looking at the first 6 characters, so as long as the variations appear AFTER the first 6 characters, they will be considered matches.

3) If the data range changes, be careful as you paste. Change the formulas to use a longer a "default" range of cells, maybe make it B28:B200...that should be sufficient, yes?

[DonkeyOte]

Another option would be to adapt the possible solution you were given on the previous thread, ie:

C7:
=SUMPRODUCT(--ISNUMBER(MATCH($B$28:$B$60,INDEX($I$7:$L$14,MATCH($B7,$I$7:$I$14,0),0),0)),--($C$28:$C$60=C$6))
copied across C7:E14

the above uses the term in B7 and searches for all known variants thereof (as stipulated by table in I:L) in the main range B28:B60... the alteration from before is the addition of the Cash/Credit/Operator check.

[sumonrezadu]

Thanks, Thanks a lot JBeaucaire and DonkeyOte. You helped a great. Actually my understanding was wrong. JBeaucaire I am sorry for that. Thanks again so much.