wild card with if logical function

[b166er]

hi members,

i need help on how to use wild cards(*/?) with if logical function.

please provide me example.

[Mikeopolo]

It's better if you tell us more about what you want to do; then someone may think of ways to solve your problem. I don't know of any wildcards with IF functions, but something I DO know may be just what your problem needs.

Regards
mike

[b166er]

thankyou for reply here is my problem

A1:A10 contains different jobnumbers in that format 32/2240,32/2425,32/4040 and so on and B1:B10 contains amount.

job code 32/4040 first two diget for budget head(32) second for expence(40)

third for location(40).

in cell C1:c10 i just want to show particular location amount if there would have been wild cards i could get that by using following format

A1:A10=32/??40 or A1:A10=32/??25 and so forth how can i get that result.

[Mikeopolo]

OK, here's one way:

With data as you describe, in range A1:J2,
and with row 1 A:J named jobs
and with row 2 A:J named data

in K2 type:

=SUMPRODUCT((LEFT(jobs,2)="32")*(RIGHT(jobs,2)="40")*(data))

The values of 32 and 40 could be stored outside this cell; say they are held in L2 and M2, then the formula can be written:

=SUMPRODUCT((LEFT(jobs,2)=TEXT(L2,"0"))*(RIGHT(jobs,2)=TEXT(M2,"0"))*(data))

Hope this is helpful

Regards
Mike

[Toppers]

Is this acceptable?

=SUMPRODUCT(--(RIGHT(A1:A10,2)="40"),--(B1:B10))

"b166er" wrote:

>
> thankyou for reply here is my problem
>
> A1:A10 contains different jobnumbers in that format
> 32/2240,32/2425,32/4040 and so on and B1:B10 contains amount.
>
> job code 32/4040 first two diget for budget head(32) second for
> expence(40)
>
> third for location(40).
>
> in cell C1:c10 i just want to show particular location amount if there
> would have been wild cards i could get that by using following format
>
> A1:A10=32/??40 or A1:A10=32/??25 and so forth how can i get that
> result.
>
>
> --
> b166er
> ------------------------------------------------------------------------
> b166er's Profile: http://www.excelforum.com/member.php...o&userid=34912
> View this thread: http://www.excelforum.com/showthread...hreadid=547358
>
>

[Ron Coderre]

b166er

Try this:

This formula sums the Col_B values where Col_A begins with "32/" and ends with "40":

=SUMIF(A1:A10,"32/*40",B1:B10)

Does that help?

Regards,
Ron

[Mikeopolo]

Nice method Ron, thanks for sharing it!

Mike