Excel 2000: Trouble Totaling 1

[goopit]

Hello,

I was given a file today by one of the bosses. He absolutely will not let me re-format his data to make it easy to total. He likes the way it looks on the page.

There are two columns: # of stores Status
The rows look like: 59 Active
6 Inactive
12 Clearanced
23 Active
and so on.

I need something that will total the number of Active stores, Inactive and Clearanced ones. Like I said, re-formatting the page is right out. Any ideas?

Thanks,

Goopit

 

[AnotherHiggins]

Try

=countif(B:B,"Active")

or, if you need to sum the numbers in column A for active stores, then try

=sumproduct(--(A1:A65535)*--(B1:b65535="Active"))

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[AnotherHiggins]

Come to think of it, you don't even have to use SumProduct for this, you can just use SUMIF:

=sumif(B1:B65535,"Active",A1:A35535)

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[goopit]

John,

The second formula there is a beauty! Thank you very much for your quick and lovely answer.

Goopit

 

[AnotherHiggins]

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.