wildcard in concatenate

[Peter-Jan De Smedt]

Hi I'm trying to use ""*$" in a concatenate function but excel does't accept this.

The problem seems to be with the second " after the first one. Excel accepts '' but then this does not work for the sumifs.

complete formula is

=CONCATENATE(""*$";IF($E$4="all";"";$E$4);"$*''&"*$";IF($E$8="all";"";$E$8);"$*"&"*$";IF($E$12="all";"";$E$12);"$*"&"*$";IF($E$17="all";"";$E$17);"$*"").

I want to use the output in a sumifs function to avoid an explosion of separate criteria.

Anybody's help would be much appreciated!

 

[lhkittle]

I changed your formula removed ; put in ,.

For each ""*$" I put in H1, where cell H1 = $.

I entered "all" in each of the cells looking for "all".


Formula returned seven $'s.


I entered 50 in D4 and formula returned 50$$$$$$$.


=CONCATENATE(D4,IF($E$4="all","",$E$4),H1&H1,IF(E8="all","",D8),H1&H1,IF($E$12="all","",$D$12),H1&H1,IF($E$17="all","",$D$17),H1)


I have no idea if this will help.


Regards,

Howard

 

[NARAYANK991]

Hi Peter ,


If Howard's answer resolves your problem , my question doesn't matter , otherwise I am interested to know what you wish to do.


1. Where is this formula entered ? Suppose we assume it is some cell , say A1.


2. What should A1 contain for the following conditions :


a. E4 = "all"

b. E4 is not equal to "all"

c. E8 = "all"

d. E8 is not equal to "all"

e. E12 = "all"

f. E12 is not equal to "all"

g. E17 = "all"

h. E17 is not equal to "all"


What should happen when multiple conditions are satisfied , or if none of the conditions is satisfied ?


Narayan

 

[Peter-Jan De Smedt]

Thanks for you reply.

I was able to solve the matter in another way.

I needed the output of the concatenate to use in a sumif. I assigned a key to every condition eg apples = l , germany = k and I used a ? in case of an all value so that all values where summed for condition x in the corresponding place. say i wanted to sum all apples from all countries i would have *l?* as output for my concatenate.

PJ