SUMIFS with numbers as criteria and wildcard

[norts55]

Hello,
I am trying to write a formula that will sum all of column L (L:L) if column A (A:A) has cells that start with 643 and 647.

Any help would be appreciated as I have been struggling with this. It seems like I am missing something simple.

Thanks in Advance

=SUMIFS(L:L,A:A,"643*",A:A,"647*")

 

[RoryA]

Wildcards don't work with numbers, and even if they did, your formula is ANDing the criteria, which is not possible. You could SUMPRODUCT, or an array formula, or FILTER if you have it (your profile is incomplete on that front) but best to avoid whole column references.

 

[norts55]

Thanks for the response. I tried SUMPRODUCT but that didn't seem to be what I was looking for.

Maybe this screen shot will help. Column A:A has cells that actually look like they do below (Maybe this will be able to be used in a formula somehow). But there are hundreds of possibilities and the list could grow or shrink by the day. I just need to extract and sum the ones that start with 643 and 647 with a formula so I can easily paste this formula in multiple sheets to get my desired result.

 

[Scott Huish]

It seems like SUMPRODUCT should work:
=SUMPRODUCT(((INT(A2:A100)=643)+(INT(A2:A100)=647)),L2:L100)

 

[norts55]

It seems like SUMPRODUCT should work:
=SUMPRODUCT(((INT(A2:A100)=643)+(INT(A2:A100)=647)),L2:L100)

I must be missing something. Because I get #VALUE!. Does this work for you???

 

[norts55]

I found this thread that seems to be very close to the same question I have. This worked for them but whatever I try I just cannot get it to work.

SUMIF with INT

Hi, I have this table (its spanish so decimals are commas) I want it to add items as a new total in G66 if the integer in column a is the same as the reference number in A66, but it seems it wont work on a range. =SUMIF(A14:A58;INT(A14:A58)=A66;G14:G58) Thanks for the help

www.mrexcel.com

 

[Cubist]

Your latest data had new additional rows that has alpha characters. What suggested won't work. Basically you have a mix of numerical and text data. You'd need to convert all of them to text.

 

[norts55]

Ah Ha! Thank you so much. You found the culprit. After I removed those, Scott Huish's formula (=SUMPRODUCT(((INT(A2:A100)=643)+(INT(A2:A100)=647)),L2:L100)) works. But now I have to filter out those lines with alpha characters.

 

[norts55]

So those lines with the alpha characters could show up at any time. So, somehow I need to filter those out. Would adding "If(ISNumber" somewhere in the formula work? Again, I have tried to place this in and I am having no luck.

 

[Phuoc]

Try this:

=SUMPRODUCT((LEFT(A2:A100,3)="643")+(LEFT(A2:A100,3)="647"),L2:L100)