excel Sum table with duplicates

Factual Questions
1

Hi guys,

Im trying to do a stock sheet.

On sheet one i have 3 colums,
colum one has the stock name,
colum three has the stock amount

these stack names and amounts however are duplicated throughout sheet one.
so
A B C
1 Apple red fruit 2
2 Banana fruit 5
3 Cheese gouda Dairy 6
4 Apple red fruit 8

etc…

now on sheet 2 i would like a total of all apple’s red quantity from sheet 1

can anyone help, i know its a easy formula, i just somehow forgot it

Regards

2

=Sumif(A:A, “Apple”, C:C)

Assuming A has the stock name and C has the stock quantity.

You can also have the middle term of the function reference another cell instead of static “Apple”, so you can have a dynamic formula that copies down and can return the amounts of any stock name in the target cell.

Sumif’s are easily my favorite function.

3

ok but now if i want the sum on sheet to to referance sheet 1?
so sheet one has the name and the quantity but they are duplicated throughtout sheet 1

also its not just “apple” lets say its “apple green” do i leave a space or one word?

thanks

4

Runs a close second to INDIRECT in my book

5

If you place the formula on sheet 2 it would look like this

**=Sumif(Sheet1!A:A, “Apple”, Sheet1!C:C)
**
Also the text would go between quotes exactly as written with spaces -

“Apple”
“Red Apple”

6

You can replace the text in that formula with whatever cell the text is in as well. For example if you’ve labeled the table you want to populate with “Apple” in cell A1, then A1 can replace “Apple” in the formula. Just make sure to anchor it correctly otherwise copying the formula out or down will go kaflooey on you.

7

thanks allot guys, problem solved

XXX