summing data in multiple columns
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 |
summing data in multiple columns
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 |
summing data in multiple columns
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 |
summing data in multiple columns
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 |
summing data in multiple columns
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 |
All times are GMT +1. The time now is 05:40 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com