I'm looking for a formula to lookup criteria in column A and add the results
in columns B, C, D, E, etc.

For example I would like to know the sum of how many "CATS" have been sold
in a given year and I will be adding addtional months as time progresses and
do not want to have to adjust my range or formula each time a new months
worth of data is added. For example.

column A column B column C column D column E

DOGS 100 200 150 50
CATS 200 300 100 0
FISH 50 100 200 100

Any help is truly appreciated.

Thanks,

Brad

Hi,

Try this

=SUMPRODUCT((A1:A200="Dogs")*(B1:E200))

It doesn't matter if the lower part of the range are empty
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"brad1824" wrote:

I'm looking for a formula to lookup criteria in column A and add the results
in columns B, C, D, E, etc.

For example I would like to know the sum of how many "CATS" have been sold
in a given year and I will be adding addtional months as time progresses and
do not want to have to adjust my range or formula each time a new months
worth of data is added. For example.

column A column B column C column D column E

DOGS 100 200 150 50
CATS 200 300 100 0
FISH 50 100 200 100

Any help is truly appreciated.

Thanks,

Brad

Mike,

I tried this formula (I put it in cell G1) and I got back #VALUE!

Am I missing an add-in perhaps? any ideas?

Thanks.

Brad

"Mike H" wrote:

Hi,

Try this

=SUMPRODUCT((A1:A200="Dogs")*(B1:E200))

It doesn't matter if the lower part of the range are empty
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"brad1824" wrote:

I'm looking for a formula to lookup criteria in column A and add the results
in columns B, C, D, E, etc.

For example I would like to know the sum of how many "CATS" have been sold
in a given year and I will be adding addtional months as time progresses and
do not want to have to adjust my range or formula each time a new months
worth of data is added. For example.

column A column B column C column D column E

DOGS 100 200 150 50
CATS 200 300 100 0
FISH 50 100 200 100

Any help is truly appreciated.

Thanks,

Brad

I actually got it to work just now. Thanks so much.

"brad1824" wrote:

Mike,

I tried this formula (I put it in cell G1) and I got back #VALUE!

Am I missing an add-in perhaps? any ideas?

Thanks.

Brad

"Mike H" wrote:

Hi,

Try this

=SUMPRODUCT((A1:A200="Dogs")*(B1:E200))

It doesn't matter if the lower part of the range are empty
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"brad1824" wrote:

I'm looking for a formula to lookup criteria in column A and add the results
in columns B, C, D, E, etc.

For example I would like to know the sum of how many "CATS" have been sold
in a given year and I will be adding addtional months as time progresses and
do not want to have to adjust my range or formula each time a new months
worth of data is added. For example.

column A column B column C column D column E

DOGS 100 200 150 50
CATS 200 300 100 0
FISH 50 100 200 100

Any help is truly appreciated.

Thanks,

Brad

One way...

=SUM(INDEX(B2:M4,MATCH("cats",A2:A4,0),0))

--
Biff
Microsoft Excel MVP


"brad1824" wrote in message
...
I'm looking for a formula to lookup criteria in column A and add the
results
in columns B, C, D, E, etc.

For example I would like to know the sum of how many "CATS" have been sold
in a given year and I will be adding addtional months as time progresses
and
do not want to have to adjust my range or formula each time a new months
worth of data is added. For example.

column A column B column C column D column E

DOGS 100 200 150 50
CATS 200 300 100 0
FISH 50 100 200 100

Any help is truly appreciated.

Thanks,

Brad