Sumif with about 1,000 criterias

Hello,

I need to sum up about 1000 different items from a .txt import, each with a unique itemcode. The item codes I would sum up are on a chart, so I can add new itemcodes I have to list when necessary.

Currently the best I could come up with is use SUMIF + SUMIF + SUMIF + SUMIF... but I would have to do that about 1000 times.

Any help is appreciated! I have included a sample excel sheet.

Thanks in advance.

I'm pretty sure I understood what you wanted. I have made the codes text which means they must be entered with a preceding apostrophe (i.e. the ') . I put conditional formatting on the codes to help you know when you have the same one more than once in your list. I put the totals to the left of the codes. I hope this is what you were looking for.

Why did you list repeat codes in your group1.. is that done purposely.

If so, you should create a unique list with Data|Advanced Filter..

then use something like:

=SUMPRODUCT(SUMIF(A:A,J6:J1000,C:C))

where J6:J1000 is a unique listing of codes to search...

Adjust J6:J1000 to location of unique list.

Something like

=SUMPRODUCT(--(LEFT(A:A,4)="2123"),C:C)

Or you can convert the column to text

Select column and go to Data|Text to Columns and skip to the 3rd dialogue window... choose Text from the column data format section.

Now you can use SUMIF with the wildcard...

Hmm I've been trying to get your formula to work for a while now with no success. Here is what I'm using

=SOMARPRODUTO(--(ESQUERDA($A:$A;4)=$G$1:$G$4);$C:$C)

=SOMARPRODUTO(--(ESQUERDA($A:$A;4)="2123");$C:$C) doesn't work either.

maybe the "--" is something else in portuguese?

As for my previous formula using *, I tried formatting the column to text but that doesn't help either. However, if I write some gibberish after the number, it works.

Are you in 2003 or 2007?

2003 doesn't allow whole column references such as A:A with this formula.

Perhaps attach the workbook... the formulas will translate to me automatically... if there is a problem and I can fix it, when I attach it back it should translate back to your language.

I did not realize you were still trying to match with a group of numbers....

I used this formula:

=SUMPRODUCT(--ISNUMBER(MATCH(LEFT(A:A,4)&" ",$J$6:$J$30&" ",0)),D:D)

Note, though, this is quite resource intensive and you should try not to use whole column references for this kind of work... try shortening the ranges to max you think you will need.