Formula help required

[Domenic]

Hi,

Sheet 1
---------

Batch Numbers Components Weights
1000 Oranges 3
1001 Apples 5
1000 Oranges 6
1001 Apples 7
1001 Grapes 8
1002 Grapes 10

Sheet 2
---------

Apples Grapes Oranges
1000 0 0 9
1001 12 8 0
1002 0 10 0


Put the following formula in B2 in Sheet 2, copy across and down:

=SUMPRODUCT((Sheet1!$A$2:$A$7=Sheet2!$A2)*(Sheet1!$B$2:$B$7=Sheet2!B$1),S
heet1!$C$2:$C$7)

Hope this helps!

 

[Kevin H. Stecyk]

Greg,

You might not have noticed but your pc clock is set incorrectly. You might
wish to reset it so that you messages fall in order with everyone else's
messages. Some people might make the mistake that you set your clock ahead
on purpose so that you messages would remain on top and you would get more
visibility. Of course, that is rude and I know you would not do that. So
you might wish to take note that you clock is incorrectly set, and reset it
to the proper time.

Best regards,
Kevin

 

[Anders Silven]

Hi Gregor,

One way is to use a Data>Pivot Table. With just a few mouse clicks you can get
something like this from the sample data:

Sum of Weights
Batch Numbers Total
1002 10
1001 20
1000 9
Total 39

In addition to Excel Help there are good instructions here:
http://www.contextures.com/tiptech.html
http://peltiertech.com/Excel/Pivots/pivotstart.htm

HTH
Anders Silven

 

[Domenic]

Hi,

No problem. Then put this formula in B2 of Sheet 2 and copy down:

=SUMIF(Sheet1!$A$2:$A$7,A2,Sheet1!$C$2:$C$7)

Adjust the range to suit your data.

Cheers!

 

[pikapika13]

I haven't really tested this yet...but try adding this in your column in
Sheet2:
=SUMIF(Sheet1!$A$1:$A$200,A1,Sheet1!$C$1:$C$200)


assuming you have 200 rows; you can change that....
Good luck

 

[pikapika13]

Sorry Domenic...
use the "$C:$C" shown below; it'll look at the entire column.

=SUMIF(Sheet1!$A$1:$A$200,A1,Sheet1!$C:$C

 

[Anders Silven]

pikapika13 and Domenic,

GregorK wants something like

Sheet 2
---------

Batch Numbers Total Weight (Sum of all components in batch)
1000 9
1001 20
1002 10

How do you figure that with your formulas?

Regards
Anders Silven

 

[Peo Sjoblom]

First extract the unique batches, note that the formula can't be in the
first row,
in this example it is in A2 sheet

=INDEX(Sheet1!$A$2:$A$10,MATCH(0,COUNTIF($A$1:A1,Sheet1!$A$2:$A$10),0))

assuming the batches are in A2:A10

entered with ctrl + shift & enter

copy down until you get a zero or an error

now in B2 sheet use

=SUMIF(Sheet1!$A$2:$A$10,A2,Sheet1!$C$2:$C$10)

copy down along side the formulas in A

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[pikapika13]

I agree that a pivot table works as well (my preference, actually).
However, the formulas works, Anders. I tried attaching my file that I
tested, but I'm having problems attaching it.
Column C in sheet2 should have that formula in the same row as each
unique batch number.
Column A Column B
Batch Numbers Total Weight
1000 =SUMIF(Sheet1!$A$1:$A$200,A1,Sheet1!$C:$C)

1001 =SUMIF(Sheet1!$A$1:$A$200,A2,Sheet1!$C:$C)


1002 =SUMIF(Sheet1!$A$1:$A$200,A3
,Sheet1!$C:$C)

 

[Domenic]

pikapika13 and Domenic,

GregorK wants something like


How do you figure that with your formulas?

Regards
Anders Silven

Well, my formula will give him what he wants, but he'll have to enter
the unique batch numbers first. Of course, he always has the option of
using Peo's formula to extract them, especially if they're numerous.

 

[gregork]

Hi,
On sheet 1 I have batch numbers in column A. In column B, I have components
i.e oranges, apples, etc. In column C, I have the weights of each
component.In column 1 there can be many identical batch numbers.
On sheet 2, I want to list each individual batch number from sheet 1(in
column A) and then sum all the component weights that correspond to that
number on sheet 1(in column B). I have no idea how to do this.

gregorK

 

[gregork]

Thanks everyone for your help. The formulas provided work well. Peo's
formula for extracting unique batch numbers is great as it saves me
inputting them manually but I have a problem when I get a zero
result....after the zero cell I get an ugly "#N/A" in the next cell down the
column. My list of batch numbers is continually expanding so I need to have
the formula continuing down the column to allow for new entries. Any ideas
on how to get rid of the "#N/A" ?

Many thanks
gregorK

 

[Peo Sjoblom]

One way if you can live with the zero in the first mismatch

=IF(ISNA(MATCH(0,COUNTIF($A$1:A2,Sheet1!$A$2:$A$1000),0)),"",INDEX(Sheet1!$A
$2:$A$1000,MATCH(0,COUNTIF($A$1:A2,Sheet1!$A$2:$A$1000),0)))

entered with ctrl + shift & enter

will return blanks instead of the errors if you copy it down for future
input in sheet1

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[gregork]

Many thanks for your reply Domenic. Sorry I didn't explain myself
properly......what I want in column B of Sheet 2 is the sum total weight of
all the components that have the same batch number on Sheet 1. e.g :

Sheet 1
---------

Batch Numbers Components Weights
1000 Oranges 3
1001 Apples 5
1000 Oranges 6
1001 Apples 7
1001 Grapes 8
1002 Grapes 10

Sheet 2
---------

Batch Numbers Total Weight (Sum of all components in batch)
1000 9
1001 20
1002 10


Kind regards
GregorK

 

[gregork]

My apologies. I had the right time set but in the wrong time zone.

gregorK