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

genius7

Board Regular
Joined
Oct 28, 2013
Messages
56
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!



vegetabletable1JanuaryFebruaryMarchAprilJuneJulyAugustSeptember
carrotpeanut1010171073
radishapple81877824
turnipbanana105418327
cucumbercarrot24937455
cabbageradish611070891
fruitsgrape049102471
applehazelnut5235379
pearpear9581099
bananaraspberry47711
grapewalnut0343
raspberrycucumber201
nutscabbage52
hazelnutturnip1
walnutcoconut5
peanutpine nut
coconuttotal3630524745525852
pine nut
table2JanuaryFebruaryMarchAprilJuneJulyAugustSeptember
vegetable
fruits
nuts

<tbody>
</tbody>
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
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</SPAN>
table1</SPAN>
January</SPAN>
February</SPAN>
March</SPAN>
April</SPAN>
June</SPAN>
July</SPAN>
August</SPAN>
September</SPAN>
2
carrot</SPAN>
peanut</SPAN>
10</SPAN>
10</SPAN>
1</SPAN>
7</SPAN>
1</SPAN>
0</SPAN>
7</SPAN>
3</SPAN>
3
radish</SPAN>
apple</SPAN>
8</SPAN>
1</SPAN>
8</SPAN>
7</SPAN>
7</SPAN>
8</SPAN>
2</SPAN>
4</SPAN>
4
turnip</SPAN>
banana</SPAN>
10</SPAN>
5</SPAN>
4</SPAN>
1</SPAN>
8</SPAN>
3</SPAN>
2</SPAN>
7</SPAN>
5
cucumber</SPAN>
carrot</SPAN>
2</SPAN>
4</SPAN>
9</SPAN>
3</SPAN>
7</SPAN>
4</SPAN>
5</SPAN>
5</SPAN>
6
cabbage</SPAN>
radish</SPAN>
6</SPAN>
1</SPAN>
10</SPAN>
7</SPAN>
0</SPAN>
8</SPAN>
9</SPAN>
1</SPAN>
7
fruits</SPAN>
grape</SPAN>
0</SPAN>
4</SPAN>
9</SPAN>
10</SPAN>
2</SPAN>
4</SPAN>
7</SPAN>
1</SPAN>
8
apple</SPAN>
hazelnut</SPAN>
5</SPAN>
2</SPAN>
3</SPAN>
5</SPAN>
3</SPAN>
7</SPAN>
9</SPAN>
9
pear</SPAN>
pear</SPAN>
9</SPAN>
5</SPAN>
8</SPAN>
10</SPAN>
9</SPAN>
9</SPAN>
10
banana</SPAN>
raspberry</SPAN>
4</SPAN>
7</SPAN>
7</SPAN>
1</SPAN>
1</SPAN>
11
grape</SPAN>
walnut</SPAN>
0</SPAN>
3</SPAN>
4</SPAN>
3</SPAN>
12
raspberry</SPAN>
cucumber</SPAN>
2</SPAN>
0</SPAN>
1</SPAN>
13
nuts</SPAN>
cabbage</SPAN>
5</SPAN>
2</SPAN>
14
hazelnut</SPAN>
turnip</SPAN>
1</SPAN>
15
walnut</SPAN>
coconut</SPAN>
5</SPAN>
16
peanut</SPAN>
pine nut</SPAN>
17
coconut</SPAN>
total</SPAN>
36</SPAN>
30</SPAN>
52</SPAN>
47</SPAN>
45</SPAN>
52</SPAN>
58</SPAN>
52</SPAN>
18
pine nut</SPAN>
19
20
table2</SPAN>
January</SPAN>
February</SPAN>
March</SPAN>
April</SPAN>
June</SPAN>
July</SPAN>
August</SPAN>
September</SPAN>
21
vegetable</SPAN>
8</SPAN>
5</SPAN>
19</SPAN>
10</SPAN>
7</SPAN>
14</SPAN>
19</SPAN>
10</SPAN>
22
fruits</SPAN>
18</SPAN>
10</SPAN>
30</SPAN>
27</SPAN>
32</SPAN>
32</SPAN>
21</SPAN>
22</SPAN>
23
nuts</SPAN>
10</SPAN>
15</SPAN>
3</SPAN>
10</SPAN>
6</SPAN>
6</SPAN>
18</SPAN>
20</SPAN>

<TBODY>
</TBODY>
 
Upvote 0
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
FoodTrackerExample_zps5639bbd9.jpg
 
Upvote 0
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)))

vegetabletable1JanuaryFebruaryMarchAprilJuneJulyAugustSeptember
carrotpeanut1010171073
radishapple81877824
turnipbanana105418327
cucumbercarrot24937455
cabbageradish611070891
fruitsgrape049102471
applehazelnut5235379
pearpear9581099
bananaraspberry47711
grapewalnut0343
raspberrycucumber201
nutscabbage52
hazelnutturnip1
walnutcoconut5
peanutpine nut
coconuttotal3630524745525852
pine nut
Total
table2JanuaryFebruaryMarchAprilJuneJulyAugustSeptember
vegetable
fruits
nuts

<tbody>
</tbody>
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top