SUMIF across sheets and row/columns

[cosmopolicious]

Hi,

Beginner Excel user here....
I have a summary sheet that I'd like to use to show me how many items have been sold in each Farm for a specific time frame.
I understand that SUMIF won't work because my range is a column but my sum_range is a row. The criteria has to be Item Code (Column A). I can't figure out the work-around. I've read up on SUMPRODUCT and TRANSPOSE but keep getting errors.

I've added sample data below.
Results should show:
Apples = 4
Cherry = 2
etc.

Appreciate your help! Thanks in advance.

Summary Sheet
[TABLE="width: 351"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Item Code[/TD]
[TD]Item Name[/TD]
[TD]Day 1-3[/TD]
[TD]Day 4-6[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]100[/TD]
[TD]Apple[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]101[/TD]
[TD]Cherry[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]102[/TD]
[TD]Orange[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]103[/TD]
[TD]Kiwi[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]104[/TD]
[TD]Pear[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]105[/TD]
[TD]Strawberry[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]106[/TD]
[TD]Melon[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]107[/TD]
[TD]Grape[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Farm 1
[TABLE="width: 501"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Item Code[/TD]
[TD]Item Name[/TD]
[TD]Day 1[/TD]
[TD]Day 2[/TD]
[TD]Day 3[/TD]
[TD]Day 4[/TD]
[TD]Day 5[/TD]
[TD]Day 6[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]100[/TD]
[TD]Apple[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]101[/TD]
[TD]Cherry[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]102[/TD]
[TD]Orange[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]103[/TD]
[TD]Kiwi[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]104[/TD]
[TD]Pear[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]105[/TD]
[TD]Strawberry[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]106[/TD]
[TD]Melon[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]107[/TD]
[TD]Grape[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

Farm 2, 3, 4 etc would be on different sheets with the same items but different quantities.

 

[oldbrewer]

maybe do the sums on each sheet then pull those values to a helper sheet, do the sums and pull the answers to your main summary sheet ?

 

[cosmopolicious]

maybe do the sums on each sheet then pull those values to a helper sheet, do the sums and pull the answers to your main summary sheet ?

Hmm... That works, but I'm still hoping for a more elegant solution as I want to avoid having to add sums for each date range on each sheet (I have 20 different "farm" sheets and 365 days worth of sales data, that I'm dividing into weeks actually).

 

[oldbrewer]

so put ALL your data on one sheet each row would be farm number, item code, fruit, date, amount and analyse that using either sumproduct or a pivot table...