Sumifs Wildcard and Left

[Ruca13]

Hello Everyone.

I'm trying to sum values for a given account. In this example the cell (B25) is "723*". The formula should sum all of those that start with 723, but i cannot keep the wildcard working.

=sumifs(Dados!$P$2:$P$300000,Dados!$E$2:$E$300000,left($B25,len($B25)-1)*1&"*")

I know about sumproduct, but in this case I don't want to use it. It makes the file slower to calculate and explaining how the formula works to others that don't use excel everyday is challenging to say the least.

Thank you for your help.

 

[Jonmo1]

I can't be sure, but I think you would get better performance from sumproduct by changing the syntax a little

Change
SUMPRODUCT(((Dados!$O$2:$O$250000=DESVIOS!$C7*1)*(Dados!$B$2:$B$250000=DESVIOS!R$1)*(MID(Dados!$E$2:$E$250000,5,1)=MID(DESVIOS!$B7,5,1))*(LEFT(Dados!$E$2:$E$250000,3)=LEFT($B7,3))),Dados!$P$2:$P$250000),
to
SUMPRODUCT(--(Dados!$O$2:$O$250000=DESVIOS!$C7*1),--(Dados!$B$2:$B$250000=DESVIOS!R$1),--(MID(Dados!$E$2:$E$250000,5,1)=MID(DESVIOS!$B7,5,1)),--(LEFT(Dados!$E$2:$E$250000,3)=LEFT($B7,3)),Dados!$P$2:$P$250000),

 

[Ruca13]

Thanks Jonmo1.

My resistance isn't only based on performance (i've diminished the number of rows to 100000, it would be riskier to make less, and I cannot make variable to use the last non-blank because there are blank cells in the middle). It is not a file I will be using, I just make the solution in excel. My colleague does not want sumproduct because is absolutely clueless about how to use it, and if in the future any change needs to be made, then he would not be able to do it himself.

I will try to "force" him to allow me to have helper columns, and I will use text(cell, "0") to transform to text instead of text to columns, since it won't need to be repeated in future this way.

About your sumproduct suggestion, I'm struggling to make the "?" part variable, so I will only be testing your suggestion after having the formula functional again.

Thank you for your efforts.

 

[Jonmo1]

and I cannot make variable to use the last non-blank because there are blank cells in the middle).

That doesn't mean you can't have a variable for the last non blank row.

This will get the last non blank row# in column P (assuming numeric values in P, because that's the column being SUMMED)
=MATCH(99.99999999999999E+307Dados!$P:$P)

 

[Ruca13]

I used:

=MATCH(99.99999999999999,Dados!$P:$P)

not sure what you meant above but this worked (with indirect of course). It was visible that the formula was improved.

About using the "--" in the sumproduct, unless I could time it in milliseconds, no difference at naked eye.

I will consider it closed and develop an alternate file using helper columns but without pressure since there's a solution working.

Thank you for your expertise.