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.

 

[RoryA]

Wildcards won't work if your account number data is actually stored as numbers. They would have to be stored as text, or you need to use a mathematical operator like > or < to filter them.

 

[Ruca13]

So, given it is indeed stored as numbers in the sheet "Dados", i must use sumproduct from what I've been trying.

The greater than will not work since the number it should be greater only starts with that, e.g. 723 can't be higher than 7231, and there's no way (from what i know) to change the whole column without using sumproduct.

Thank you for your help anyway.

 

[63falcondude]

and there's no way (from what i know) to change the whole column without using sumproduct.

Highlight the column, Text to Columns, Next, Next, Text, Finish.

 

[snjpverma]

Ruca13, You can use a helper column and put this formula to extract the 3 leftmost digits. Then give helper column to the criteria range of SUMIFS.

=left(92,3)

 

[Jonmo1]

Since you're calling it an Account number, is it a Fixed number of digits?

If the account number is say 6 digits long, then you need criteria to be
Greater than or equal to 723000 AND Less than or equal to 723999

=sumifs(Dados!$P$2:$P$300000,Dados!$E$2:$E$300000,">=723000",Dados!$E$2:$E$300000,"<=723999")

Add/Remove 0's and 9's according to the 'Fixed' number of digits in column E

 

[Ruca13]

Thank you for all the replies.

Unfortunately, the tables where I'm working are pretty strict, so it would be for the best to avoid adding columns. I know it would make the case fairly simple, but it's something I want to leave as an "hail mary" option.

To Jonmo1 especifically, no, the number is not fixed. Some subaccounts are disaggregated with more digits. that's why the wildcard was so important.

But thank you all.

 

[Jonmo1]

Try
=SUMPRODUCT(--(LEFT(Dados!$E$2:$E$300000,3)=LEFT($B25,LEN($B25)-1)),Dados!$P$2:$P$300000)

 

[Ruca13]

Thank you but as I said, I was trying to simplify the formula, avoiding sumproduct. I already have it like that (although I'm trying to improve it):

=IF(NOT(ISERROR(SEARCH("~?",$B7))),
-1*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),
IF(NOT(ISERROR(SEARCH("~*",B7))),
-1*SUMPRODUCT((((LEFT(Dados!$E$2:$E$250000,SEARCH("~*",$B7)-1))=LEFT($B7,SEARCH("~*",$B7)-1))*(Dados!$O$2:$O$250000=DESVIOS!$C7*1)*(Dados!$B$2:$B$250000=DESVIOS!R$1)),Dados!$P$2:$P$250000),
-SUMIFS(Dados!$P:$P,Dados!$B:$B,R$1,Dados!$O:$O,CONCATENATE($C7),Dados!$E:$E,CONCATENATE($B7))))

 

[Jonmo1]

You seem to be stuck between a Rock and a Hard Place.

Wildcards don't work on numeric values.
There's no getting around that.

Your choices seem to be
Add a helper column to extract the left 3 digits and use that column in sumifs.
Convert the Account numbers from Numeric to Text using Text To Columns suggested in post #4, then wildcards will work.
Use Sumproduct.


Your resistance to sumproduct is based on performance right?
Does your data REALLY have 250000 rows ?
If you can restrict those ranges to a more reasonable count of rows, then sumproduct won't be so slow.