Array Construction

[M Moore]

I understand the solution of . . . .

=sumproduct(--isnumber(match(a1:100,midwest,0)),b1:b100)

Now, I am going to throw one more issue into the problem.

What if I have a third column containing text (such as "revenues" and
"expense").

I need to add all the revenues for all the midwest departments into one
cell. Or, I need to add all the expenses for all the midwest departments
into one cell.

 

[Roger Govier]

Hi Mike

Set the Text you are looking for in a cell e.g. H1
Then assuming your column containing the text is column C
=sumproduct(--isnumber(match(a1:100,midwest,0)),--(C1:C100=H1),b1:b100)

You can keep on adding conditions into the Sumproduct formula with
further sets of
,--(range=test),