Formula to look in range

[almouchie]

I dont know how to explain this really
I have a range with amounts in it
& another amount which could be the addition of 2 or more cells in this range

how can I have a formula that looks at this cell(specified amount) & then look in all the range to find a match whether it is found in one of the cells in the range or is the addition of several cells.
Can this be done

the reason I want to do this
is have a list of amounts for every bill
& then an amount which could be the cumulative of all bills related to a specific services.


thanks

[XLGibbs]

Al,

You can use sumproduct for this....basic syntax below:

=SUMPRODUCT(--(RangetoCompare=Criteria1),(RangetoTotal))

You would have your 1 column range of Categories or identified services where criteria1 is the cell containing your supposed matching point of interested (or the string of what you want, such as "General")

The range to sum would be a range of equal size but the column containing your values to total up.

If you need help seeting it up, shoot me a copy of sample data..You can have multiple critera set up the same as criteria1 above, having a separate range for each one.

Just keep your ranges the same size. The -- operators for true/false into 1/0 and multiples each element of the range...so the resulting total is only where they match.

Hope that helps.

[Zack Barresse]

If only a single condition exists, I recommend using a COUNTIF or SUMIF type of formula.

The -- operators for true/false into 1/0 and multiples each element of the range...so the resulting total is only where they match.

A more in-depth explanation, with great links, found here.

[XLGibbs]

If only a single condition exists, I recommend using a COUNTIF or SUMIF type of formula.


A more in-depth explanation, with great links, found here.

Sure if you want to do it the EASY way with only 1 condition

Silly me.