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
– Aktist, Commented Aug 31, 2015 at 7:19
Then just change the j6:j775 in your formula to k6:gn775. Tell me if it works. — Balinti
– Balinti, Commented Aug 31, 2015 at 7:27
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
– Noldor130884, Commented Aug 31, 2015 at 7:29
@Balinti I've tried that but it doesn't work. The formula gives 0. — Aktist
– Aktist, Commented Aug 31, 2015 at 7:31

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

1

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.

answered Aug 31, 2015 at 7:49

Tom Sharpe

35.1k5 gold badges29 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Aktist Over a year ago

Uh, I forgot one more thing. Is it possible to modify the formula and add another condition that would check row 5, where the dates are, to count the numbers only if the dates are <=today(). I tried this, but I get the value 0. =SUM(Sheet1!$J$6:$GM$775*(LEFT(Sheet1!$I$6:$I$775;LEN(A2)-1)=LEFT(A2;LEN(A2)-1))*(Sheet1!J5:GM5<=TODAY()))

Tom Sharpe Over a year ago

Your formula looks OK to me, I'm puzzled why it isn't working. The formatting of the dates looks a bit odd on your example, maybe check that they are stored as dates and not text and that they are 2015? If you change the format to general temporarily, they should show up as a number e.g. 42246.

Aktist Over a year ago

The dates had an apostrophe ('), that's why it didn't work. Thanks for the tip.

Balinti · Accepted Answer · 2015-08-31 07:49:27Z

0

=SUM(IF(MID(Sheet1!I6:I775,1,3)="MAD",Sheet1!k6:gn775,""))

With ctrl +shift+enter

answered Aug 31, 2015 at 7:49

Balinti

1,5341 gold badge11 silver badges15 bronze badges

Collectives™ on Stack Overflow

Excel: Sum columns and rows if criteria is met

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related