need help quickly!!

I have a spreadsheet that has in column I a state abreviation. (NE, MA, etc...). In column G I have a number of subscribers. I'd like a report formula that adds up all the numbers associated with a specific state.

I'm doing it manually now and it's a pain! help!

You can use SUMPRODUCT.

=SUMPRODUCT((A1:A100="MA")*(B1:B100))

Where A is your list of abbreviations and B are your numbers to sum. Rather than type the abbreviation in the formula, you could have a drop down list with all state abbreviations and have your formula refer to that cell say C1. Then you could just select a state abbreviation from the list and the formula would return the value for that abbreviation.


=SUMPRODUCT((A1:A100=C1)*(B1:B100))

Does that help,

Steve

Hi

I would suggest using Data / Pivot Table as this is designed to summarise
information.
There is an intro to using this powerful tool here:
http://peltiertech.com/Excel/Pivots/pivottables.htm

Hope this helps.
Andy.

"alexm999" <[email protected]> wrote in
message news:[email protected]...
>
> I have a spreadsheet that has in column I a state abreviation. (NE, MA,
> etc...). In column G I have a number of subscribers. I'd like a report
> formula that adds up all the numbers associated with a specific state.
>
> I'm doing it manually now and it's a pain! help!
>
>
> --
> alexm999
> ------------------------------------------------------------------------
> alexm999's Profile:
> http://www.excelforum.com/member.php...fo&userid=4918
> View this thread: http://www.excelforum.com/showthread...hreadid=517306
>

=SUMIF(A1:B5,C1,B1:B5)
A1:B5 is the range
C1 is the Crieteria
B1:B5 adds up the numbers that meet the criteria