Countif with wildcard and cell reference

[stapuff]

I am currently using the following formula, but need to expand the formula more

=COUNTIF(OnHand!$A$2:$A$20000,C2&E2)

I only want to look at the first 8 characters of the cells on sheet OnHand!$A$2:$A$20000 using character position 1,2,3,5,7,8 of the criteria C2&E2


Example

C2&E2 = SC123456

OnHand
A2 SC123456
A3 SC123456-01
A4 SC1X3B56
A5 SC1X3B56-01
A6 SC112256
A7 SC112256-02
A8 SC1Z3Z56-123
A9 SC1Z3Z56-456
A10 SC234567

The count formula would return 6 since A2,A3,A4,A5,A8,A9 all would meet the criteria

Any thoughts on how to accomplish this would be greatly appreciated.

stapuff

 

[mikerickson]

As you said in the title, use wildcards

=COUNTIF(OnHand!$A$2:$A$20000, C2&E2&"*")

 

[Yahya]

is this what you are looking for ?

Excel Workbook
	A	B	C	D	E
2	SC123456		SC		123456
3	SC123456-01
4	SC1X3B56
5	SC1X3B56-01			2
6	SC112256
7	SC112256-02
8	SC1Z3Z56-123
9	SC1Z3Z56-456
10	SC234567
OnHand

Excel 2007

Cell Formulas
Range	Formula
D5	=SUMPRODUCT(--(LEFT(A2:A10,8)=C2&E2))

 

[stapuff]

Mike -

I do not understand your response. I do reference needing to use wildcards, but only specific locations not using the *, but using the ?

To accomplish my task I have done the following:

In column B of sheet OnHand I added a helped formula:
=LEFT(TRIM(A2),8) which returns just the left 8

In my main sheet in column D I added a helper column with the following formula in it:
=C2&LEFT(E2,1)&"?"&MID(E2,3,1)&"?"&MID(E2,5,2)

which returns SC1?3?56

then do the countif
=COUNTIF(OnHand!$B$2:$B$20000,D2) returns 6

This is ugly and far more than I wanted, but it works. Any suggestions on how to make it better?

stapuff

 

[stapuff]

I have been able to remove the helper column on the the main sheet and changed my formula to:

=COUNTIF(OnHand!$B$2:$B$20000,C2&LEFT(E2,1)&"?"&MID(E2,3,1)&"?"&MID(E2,5,2))

I would like to get rid of the helper column on sheet OnHand.

Any suggestions would be greatly appreciated.

Yahya - I did try your suggestion and several variations and all returned 0, which is not correct.

Thanks,

stapuff

 

[Yahya]

could you post sample data with expected result

 

[stapuff]

I did...in my first post.


Example

countif SC1?3?56

sheet OnHand with range
A2 SC123456
A3 SC123456-01
A4 SC1X3B56
A5 SC1X3B56-01
A6 SC112256
A7 SC112256-02
A8 SC1Z3Z56-123
A9 SC1Z3Z56-456
A10 SC234567

returns 6

 

[stapuff]

I just read "SumProduct requires numeric arrays/ranges"

so using something like this formula returns 0 because the use of "?" for wildcards must be viewed as text by the sumproduct function

=SUMPRODUCT(--(LEFT(OnHand!A2:A10,8)=C2&LEFT(E2,1)&"?"&MID(E2,3,1)&"?"&MID(E2,5,2)))

stapuff