How do I lookup a value in a table which has many results?

  • Thread starter Thread starter Mallymoo
  • Start date Start date
M

Mallymoo

I have a table of medications in one column and how many of each medication
that has been ordered. Problem is the medications are listed bout 10 times. I
need to lookup the medication and return each result (or sum them).

For example:
Panadol 2
Panadol 20
Panadol 5
Nurofen 4
Nurofen 16
Nurofen 10

I want to look up Panadol and sum how many panadols have been ordered.
Answer would be 27. Then I want to be able to do the same to Nurofen.

If this is possible, could someone please let me know.
Much appreciated.
 
Use SUMIF() as below with data in ColA and B

=SUMIF(A:A,"Panadol",B:B)

You can replace the text in the formula to a cell reference...

If this post helps click Yes
 
1. one way:

=SUMPRODUCT(($A$1:$A$6=$A$1)*($B$1:$B$6))

2. another:

=SUM(IF($A$1:$A$6=$A$1;$B$1:$B$6))
(array-enter this formula i.e. CTRL+SHIFT+ENTER)

adjust yr ranges to suit

HIH
 
Back
Top