Search code examples
excelsumifs

SUMIFS Excel Multi Criteria

I'm trying to do use SUMIFS to calculate the total QTY changes for drawings with a material ID that Matches the Pipe Indent Numbers and ignore the ones that are NOT listed the piping indent numbers column. Below is what I have so far but I know the "=D:D" looks wrong.

I don't know how to reference all the numbers in the Pipe Indent Numbers List(D:) as each their own criteria, so that if the Material ID matches ANY of the Pipe Ident Numbers it will sum it.

I need help with this because my actual Pipe Ident Numbers column have about 100 other numbers not just the 5 shown.

Also not sure would I need to have the drawing number in the formula somewhere too? Maybe that's what I'm missing...

=SUMIFS(C:C,B:B,"=D:D")

Any help would be amazing! Thanks :)

r

Solution

  • you can use

    if you want to compare row by row

    =SUMPRODUCT(c2:c6*(b2:b6=d2:d6))

    if you want to compare the value in column b with all values in column d (of you do not use Excel 365 press CTRL+SHIFT+ENTER to enter the formular)

    =SUMPRODUCT(c2:c6*(b2:b6=TRANSPOSE(d2:d6)))

    Best Regards Chris