Summing only some rows of a matrix based on condition

• Hi All,

nice to meet you, my first post here. I'd like to have your opinion:

Problem: I've one matrix of data and a tag for every row

[Blocked Image: http://img26.imageshack.us/img26/2500/tagsk.jpg]

The ranges are named Tags and Data. I need to get the total of "data" for the values in rows which have a selected value in Tags.

I've come up with this to sum for example, tag "a"

(array formula)

=SUMPRODUCT(data;(MMULT(--(tags="a");TRANSPOSE((1+0*ROW(INDIRECT("1:"&COLUMNS(data))))))))

It works but is basically not understood by most endusers, do you see other options to do this in a more "friendly" way?

• Re: Summing only some rows of a matrix based on condition

This is a bit simpler but does it I think:

=SUM(MMULT(TRANSPOSE(IF(tags="a",1,0)),data))

Andrew

• Re: Summing only some rows of a matrix based on condition

=SUMPRODUCT(data*(tags="a"))

• Re: Summing only some rows of a matrix based on condition

Quote from Herbds7;540447

=SUMPRODUCT(data*(tags="a"))

Perfect, thanks.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!