Excel: Sum columns and rows if criteria is met

Question

I have a sheet with product names in column I and then dates from there on. For each date there are numbers of how many pieces of a certain product have to be made. I'm trying to sum all those numbers based on a product type, i.e.:

 I       K       L      M     ...

        30.8.   31.8.   1.9.   ...
MAD23     2       0      45    ...
MMR32     5       7      33    ...
MAD17    17      56       0    ...

MAD:    120  (2+0+45+17+56+0)
MMR:     45  (5+7+33)

What I'm doing now is sum the row first:

=SUM(K6:GN6)

MAD23 = 47
MMR32 = 45
MAD32 = 73

And then sum those numbers in column J based on part of the product name in column I:

=SUMIF(Sheet1!I6:I775;"MAD*";Sheet1!J6:J775)

MAD = 120
MMR = 45

Is it possible to do this with just one formula per criteria?

I added column names in the post. Column J contains formula =SUM(K6:GN6). — Aktist
Then just change the j6:j775 in your formula to k6:gn775. Tell me if it works. — Balinti
You could run a SUM with the help of the MATCH ( support.office.com/en-us/article/… ) or VLOOKUP ( support.office.com/en-us/article/…) commands — Noldor130884
@Balinti I've tried that but it doesn't work. The formula gives 0. — Aktist

Tom Sharpe Tom Sharpe · Accepted Answer · 2015-08-31T07:49:37

Just trying it on those three rows, I get

=SUM($K$6:$M$8*(LEFT($I$6:$I$8,LEN(I10)-1)=LEFT(I10,LEN(I10)-1)))

which is an array formula and must be entered with CtrlShiftEnter

That's assuming that I10 is going to contain some characters followed by a colon and you want to match those with the first characters of I6:I8.

Excel: Sum columns and rows if criteria is met

2 Answers