# each data in 2 different sheets and sum in 3rd

#### Ollie at Sky

##### New Member
sheet 1

 A B C D 1 banana fruit 2 apple fruit 3 tuna fish 4 veal meat

<tbody>
</tbody>

sheet 2

 A B C 1 veal 25 2 banana 31 3 tuna 72 4 apple 43 5 banana 19

<tbody>
</tbody>

I need to have sum total of fruit ( answer is 93 ) in 3rd spread sheet.
I tried with sumifs and sum with index/match, couldn't get the formula.

I know I can use vlookup to date match from sheet 1 to sheet 2, or reverse.
Than I can have sum total of fruit, but is there any other way to have not doing that and finish at once in 3rd sheet?

Is it even possible ?

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Don't think what you're wanting to do is possible in a single formula.
The reason is that you would need to somehow create an index/lookup to return multiple values in a single array (which, to my knowledge, is not possible).
A much better (and more transparent) way would be as follows:

 A B C D 4 Banana Fruit 5 Apple Fruit 6 Tuna Fish 7 Veal Meat 8 9 10 11 12 Veal 25 Meat 13 Banana 31 Fruit 14 Tuna 72 Fish 15 Apple 43 Fruit 16 Banana 19 Fruit 17 18 19 20 Fruit 93

<tbody>
</tbody>
Where D12 to D16 is:

Code:
``=INDEX(\$C\$4:\$C\$8,MATCH(\$B12,\$B\$4:\$B\$8,FALSE))``

...and cell B20 is:

Code:
``=SUMIF(\$D\$12:\$D\$17,A20,\$C\$12:\$C\$17)``

Hope this helps.
Brettster.

I can use vlooup instead index with match?
I think they dose the same thing.

Replies
6
Views
170
Replies
0
Views
382
Replies
9
Views
234
Replies
8
Views
226
Replies
6
Views
182

1,196,028
Messages
6,012,956
Members
441,740
Latest member
IammeResources

### 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