Text search in an "IF" or "SUMIF" type statement

Posted by Jeremy Harris on July 18, 2001 11:12 PM

Hello all,

I have been trying to work out how to do the following.

I have A list of data as below and I want to pull all
values associated with one particular name and product
using a formula in each cell of the 2nd table.

LOCATION prod1 prod2 prod3
A 10
B 10
A 10 10
B 10
C 5 5
C 5
A 10

So I end up with a table

Location prod1 prod2 prod3
A 20 20
B 10 10
C 5 5 5

I suppose my question is
Is it possible to use a reference to a cell in the
comparison of the

Posted by Ian on July 19, 2001 12:15 AM

The formula for column B: for the reference to "A" would be:

{=sum((A2:A8="A")*(B2:B8))}

this is an array formula the {} are added by excel (do not type them). instead of just ENTER, press CTRL+SHIFT+ENTER

hope this helps

Ian

Posted by Aladin Akyurek on July 19, 2001 4:03 AM

SUMIF

I'll assume that your sample data occupy the range A1:D8, including the column headings/labels.

I'd suggest to make a list of names from E2 on. Also, list all products across from F1 on.

In F2 enter: =SUMIF($A$2:$A$8,$E2;B$2:B$8)

Copy this formula first across then down as far as needed.

Aladin

======== Hello all,