Ok this maybe easy but heres what I am trying to do:

I have several columns in excel (lets say a through f). I want to get a count of values in 2 columns but that have to be mutually exlcusive. Meaning that I want to know if one value exists in column a and the value exists in column c then I want a count. So say column 'a' is the color of apples and say column c is the location of the apples, in a seperate tab I want to know if the location of the apples is Raleigh and the color is also red give me a count in a seperate tab.

I am sure this is easy but having a brain fart!

6/8/2009 11:42:24 AM

here's a rigged up way to do it. somebody will come in here and tell me how dumb i am, but whatever

write an if/then that inserts a 1 in a column if the two values are equal and a 0 in the column if the values are unequal. total the column. there's the answer.

6/8/2009 11:47:41 AM

^ More or less. Are you wanting to compare the same row in the 2 columns or different rows?

If it's the same row then a simple If is all you need.

6/8/2009 1:14:40 PM

I'm not exactly sure of what information you are trying to pull out but I think you are looking for how many apples in raleigh and how many of each color apple is in raleigh?

=COUNTIF(C:C,"Raleigh") will return the number of Raleigh apples (then you can change to A:A,"red" to count red apples)

Now to count Raleigh apples that are red, this is what I'd do: Take an extra column off to the side and merge the cells to read Location, Color. So put this in another column:

=C2&","&A2

That should return something like "Raleigh,red" now you can use the countif formula from before to count all "Raleigh,red" instances as well as "Raleigh,green" "Wilmington,red" etc.

[Edited on June 8, 2009 at 3:01 PM. Reason : ]

6/8/2009 2:54:36 PM