Adding values based on different criteria

[powderskier9]

This is a bit tough to explain, but here goes. I want to add multipl
items up based on a certain field. So lets say, I have:

Fruit Sold Store
Apple 1 Toronto
Apple 2 Vancouver
Banana 1 Vancouver
Cheese 1 Toronto

Is there a formula that could add up the quantity value when a2 & a
equals apple, but leave the quantity value when its only singular. Fo
example =SUMIF(A2:A3,"Apple",C2:C3) This works, but can I add a sectio
to this formula that will still list the Sold value when it i
singular. So, I would desire the items below as final layout:

Fruit Sold Store Overall Sold
Apple 1 Toronto
Apple 1 Vancouver 2
Banana 1 Vancouver 1
Cheese 1 Toronto 1

Cheers

 

[Trevor Shuttleworth]

One way:

put the following formula in cell D2 and drag down:

=IF(A2<>A3,SUMIF(A:A,A2,B:B),"")

Regards

Trevor

 

[powderskier9]

Hi Trevor,

Thanks for your prompt reply. I have tried the formula that yo
provided based on the above example, and it did not sum the totals. An
ideas

 

[powderskier9]

Does anyone else have any ideas as to how this can be accomplished

 

[Dave Peterson]

Any chance that you just had to adjust Trevor's formula to point at column C?

=IF(A2<>A3,SUMIF(A:A,A2,C:C),"")
(In row 2)

If that doesn't help, maybe you could explain how this:

Fruit Sold Store
Apple 1 Toronto
Apple 2 Vancouver
Banana 1 Vancouver
Cheese 1 Toronto

Gets translated to this:

Fruit Sold Store Overall Sold
Apple 1 Toronto
Apple 1 Vancouver 2
Banana 1 Vancouver 1
Cheese 1 Toronto 1

I don't understand why the sum for apples isn't 3 and how the apple in vancouver
went from 2 to 1.

(Formulas can only return values to the cell holding them--they can't change
another cell.)

 

[Debra Dalgleish]

To show the total for the at the last item in repeated items:

=IF(COUNTIF(A2:A$25,A2)>1,"",SUMIF($A$2:$A$25,A2,$B$2:$B$25))

 

[powderskier9]

Thank you very much Debra. Your solution worked brilliantly. This wil
save much tedious manual adding. Thanks again.

Cheers,

Mar