make summary

[norika]

I apply data validation to select and get the following results

Cell Data Amount
D10 A 10
D11 C 11
D12 A 9
D13 B 8
D15 B 25
D16 A 18

In cell D20 to D22 to make a summary showing

Cell Data Amount
D20 A 37 (10+9+18)
D21 C 11
D22 B 33(8+25)

I just want to use formula to cater for, not vba. Is it possible?

thanks in advance

norik

 

[Gord Dibben]

norika

I assume the numbers are in E10:E16

=SUMIF($D$10:$D$16,"A",$E$10:$E$16) in D20
=SUMIF($D$10:$D$16,"C",$E$10:$E$16) in D21
=SUMIF($D$10:$D$16,"B",$E$10:$E$16) in D22

Gord Dibben Excel MVP

 

[norika]

Gord,

Thank you for your reply. Other than the sum of amount (you hav
already help me to solve, thank you), I also want to make a summary o
Data (or Category), same as the sum of amount.

eg I apply Data Validation function to choose category, may be 'B' wa
the first choice (in Cell D10) or 'C' was the second one (in Cell D11)
it is changing depending on my choice.

Is there any method to solve this problem, not vba, just usin
formula.

Thank you in advance.

norik

 

[Gord Dibben]

Norika

Not quite sure what you need.

Perhaps COUNTIF to count the A's, B's or C's?

=COUNTIF(D1016,"A")

Same for B and C.

Gord Dibben Excel MVP

 

[norika]

Gord,

Thank you for your prompt reply.

I actually want to show A, B or C word one time only upon appearing, i
Cell D20 to D22, not counting the occurence. The occurence and sequenc
are depending on my choice, my be C,A,B or B,A,C or C,A or A,B etc.

Let me to explain again
Cell Category Amount
D10 C 100
D11 A 12
D12 B 14
D13 A 1
D14 C 15

In Cell D20 to D22, it shows
D20 C (using formula to show one time only) 115 (add up all Category
- you helped me to solve it already)
D21 A 13
D22 B 14

In other case, Category B may not be selected
D10 C 9
D11 A 11
D12 A 45
D13 C 56

In Cell D20 to D22, it shows
D20 C 65
D21 A 56
D22 nothing to show

Thank you in advance.

norik