Need to compute values in 2 column's

[michael.woosley]

Hello;

I am a National Guard technician and I'm trying to determine the
following;

I have several different types of equipment from different states. I
want to count how many tanks I have that are from Kentucky and have
this result posted in a cell. I also have the same equipment that
belongs to Tennessee and I need to know how many of these same pieces
belong to Tennessee. I've tried several different formula's but with
no luck.

PLEASE HELP!!


Mike Woosley
Kentucky MATES
Ft. Knox, KY

 

[Biff]

Hi!

Assume:

A1:A100 = equipment type
B1:B100 = state

=SUMPRODUCT(--(A1:A100="tank"),--(B1:B100="Kentucky"))

Better to use cells to hold the criteria:

C1 = tank
D1 = Kentucky

=SUMPRODUCT(--(A1:A100=C1),--(B1:B100=D1))

Biff

"michael.woosley"

 

[Peo Sjoblom]

What is the way you would know where they are from, state abbreviations or
something else?

=COUNTIF(Range,"KY")

will count KY

replace Range with your actual range like for example A2:A200 or something
--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com


"michael.woosley"

 

[The Horny Goat]

I am a National Guard technician and I'm trying to determine the
following;

I have several different types of equipment from different states. I
want to count how many tanks I have that are from Kentucky and have
this result posted in a cell. I also have the same equipment that
belongs to Tennessee and I need to know how many of these same pieces
belong to Tennessee. I've tried several different formula's but with
no luck.

The easiest way (assuming your file is of any real size) would be to
create a boolean condition that would have the value 1 or 0 and then
do a count.

For instance if Column A = type of unit (e.g. tank or non-tank) and
Column B was State then Column C could be =if(A1='tank' AND
B1='KY',1,0) while D could be if(A1='tank' AND B1='TN',1,0). You then
go ctrl-D over the range of all your data and count up the 1's in
columns C and D. (If you just need the info rather than for a report
simply highlighting the range will give you the number in the bottom
right corner)

This technique isn't suitable when you have a large number of
different values in column B but is 'quick and dirty' enough for most
things. Ctrl-D is a wonderful thing!