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

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.

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

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
---------------
Jacob Skaria


"Mallymoo" wrote:

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.

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

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


On 3 Lis, 07:47, Mallymoo wrote:
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.