Adding values based on different criteria

  • Thread starter Thread starter powderskier9
  • Start date Start date
P

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
 
One way:

put the following formula in cell D2 and drag down:

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

Regards

Trevor
 
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
 
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.)
 
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))
 
Thank you very much Debra. Your solution worked brilliantly. This wil
save much tedious manual adding. Thanks again.

Cheers,

Mar
 
Back
Top