# Sum only certain cells in a column depending on values in another column

#### genius7

##### Board Regular
Hello everyone,

I'm struggling with a formula. In the table below is what I actually have in my worksheet. In the first column, the column to the extreme left (column A) there are various fruits categorized according to whether they are vegetables, fruits or nuts. In the table1, there are the same fruits but are not categorized. Further, in the table1 you can see how many units of each fruit were received during various months. Where there are no values it means that no fruit was received.
What I need to accomplish in the table2 is to have number of all the vegetables, fruits and nuts for each month.

For example, in the table2, in the vegetable row for January, there should be number 8 because only 8 units of vegetables were received during the month of January (2 carrots and 6 radishes).
Another example, in the nuts row for August, there should be number 18 because 18 units of nuts were received during the month of August (7 peanuts, 7 hazelnuts and 4 walnuts).
The important thing is that in the following months, like October, November and December I may add other vegetables, fruits and/or nuts to the column A and therefore also to the table1, thus augmenting the table1 (I would add additional rows).

I think that the formula, which goes to the table2 in order to give me the results I quoted in the examples, should first determine whether the fruit in the table1 is vegetable, fruits or nut. I tried to do that using SUMIF formula but without any success. Maybe this task require an advanced use of array formulas with which I am not very familiar.

Could you please help me with this problem? Please, bear in mind that I cannot change the structure of the tables nor of the column A.
Thank you very much in advance!

 vegetable table1 January February March April June July August September carrot peanut 10 10 1 7 1 0 7 3 radish apple 8 1 8 7 7 8 2 4 turnip banana 10 5 4 1 8 3 2 7 cucumber carrot 2 4 9 3 7 4 5 5 cabbage radish 6 1 10 7 0 8 9 1 fruits grape 0 4 9 10 2 4 7 1 apple hazelnut 5 2 3 5 3 7 9 pear pear 9 5 8 10 9 9 banana raspberry 4 7 7 1 1 grape walnut 0 3 4 3 raspberry cucumber 2 0 1 nuts cabbage 5 2 hazelnut turnip 1 walnut coconut 5 peanut pine nut coconut total 36 30 52 47 45 52 58 52 pine nut table2 January February March April June July August September vegetable fruits nuts

<tbody>
</tbody>

### Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
maybe something like...

So as you add months you would just need to drag the formulas to the right to capture the column data above....essentially. The formulas not really referencing the column heading just where it is placed in the table. Hopefully that makes sense.

D21 =SUMPRODUCT(SUMIF(\$C\$2:\$C\$16,\$A\$2:\$A\$6,D\$2:D\$16))
D22 =SUMPRODUCT(SUMIF(\$C\$2:\$C\$16,\$A\$8:\$A\$12,D\$2:D\$16))
D23 =SUMPRODUCT(SUMIF(\$C\$2:\$C\$16,\$A\$14:\$A\$18,D\$2:D\$16))

 A B C D E F G H I J K 1 vegetable table1 January February March April June July August September 2 carrot peanut 10 10 1 7 1 0 7 3 3 radish apple 8 1 8 7 7 8 2 4 4 turnip banana 10 5 4 1 8 3 2 7 5 cucumber carrot 2 4 9 3 7 4 5 5 6 cabbage radish 6 1 10 7 0 8 9 1 7 fruits grape 0 4 9 10 2 4 7 1 8 apple hazelnut 5 2 3 5 3 7 9 9 pear pear 9 5 8 10 9 9 10 banana raspberry 4 7 7 1 1 11 grape walnut 0 3 4 3 12 raspberry cucumber 2 0 1 13 nuts cabbage 5 2 14 hazelnut turnip 1 15 walnut coconut 5 16 peanut pine nut 17 coconut total 36 30 52 47 45 52 58 52 18 pine nut 19 20 table2 January February March April June July August September 21 vegetable 8 5 19 10 7 14 19 10 22 fruits 18 10 30 27 32 32 21 22 23 nuts 10 15 3 10 6 6 18 20

<TBODY>
</TBODY>

Is there a particular reason why they need to be out of order in Table 1?

If so, I just create a column between the name of the food and January and list that with an identifying code such as V, F or N. This would allow you to add new foods as you see fit and use that column as the identifier in your SUMIF statement.

You can see the code I used in the image

Thank you very much Weazel. I modified your formula, added word "Total" below the last nut in the column A and here is the formula working for me, I just add it to the table2 in the first cell and drag it to the right and downwards and it works perfectly.

=SUMPRODUCT(SUMIF(INDIRECT("\$c\$"&MATCH(C\$1,C:C,0)+1&":\$c\$"&MATCH(C\$17,C:C,0)-1),INDIRECT("\$A"&MATCH(\$C21,\$A:\$A,0)+1&":\$A"&MATCH(IF(\$C22=0,"*"&"Total"&"*",\$C22),\$A:\$A,0)-1),INDIRECT(CHAR(64+MATCH(D\$20,\$1:\$1,0))&"\$"&MATCH(C\$1,C:C,0)+1&":"&CHAR(64+MATCH(D\$20,\$1:\$1,0))&"\$"&MATCH(C\$17,C:C,0)-1)))

 vegetable table1 January February March April June July August September carrot peanut 10 10 1 7 1 0 7 3 radish apple 8 1 8 7 7 8 2 4 turnip banana 10 5 4 1 8 3 2 7 cucumber carrot 2 4 9 3 7 4 5 5 cabbage radish 6 1 10 7 0 8 9 1 fruits grape 0 4 9 10 2 4 7 1 apple hazelnut 5 2 3 5 3 7 9 pear pear 9 5 8 10 9 9 banana raspberry 4 7 7 1 1 grape walnut 0 3 4 3 raspberry cucumber 2 0 1 nuts cabbage 5 2 hazelnut turnip 1 walnut coconut 5 peanut pine nut coconut total 36 30 52 47 45 52 58 52 pine nut Total table2 January February March April June July August September vegetable fruits nuts

<tbody>
</tbody>

You're welcome, glad you got it working

Replies
14
Views
709
Replies
2
Views
141
Replies
5
Views
231
Replies
4
Views
281
Replies
13
Views
319

### Forum statistics

1,221,052
Messages
6,157,635
Members
451,426
Latest member
VinnyDoesntKnowExcelCode

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back