Become a MacRumors Supporter for $50/year with no ads, ability to filter front page stories, and private forums.

R.Youden

macrumors 68020
Original poster
Apr 1, 2005
2,093
40
Hi, I have a quik question bout Excel and any help would be fantastic.

OK I have a very large amout of data that I have collected for research in an Excel file. I have a number of columns all with values between 0 and 4. What I would like to do is count the number of times, for example. 0 appears in column A AND 3 appears in column C but only when they occur at the same time, not separately. I have looked through the help section of Excel and I can't find anything. Any help would be great, thanks.

Richard
 
I don't know if this will help, but have u tried going to Data ~> Pivot Tables? U'll have a new sheet created w/ the pivot tables and u see all ur data highly organized that way. Use the pull down menus and u can get what you're looking for.
 
The best way to do this, is to use a filter.

Highlight the data that you want to compare. Then go to data and filter. You will notice some drop down arrows on the top row. Click on the first arrow and select customize, then select equal to the value you need. Then select the next column and do the samething. that should give you the fields you need...
 
=IF(AND(A1=1,C1=3),1,0)
=IF(AND(A2=1,C2=3),1,0)
=IF(AND(A3=1,C3=3),1,0), etc.


The above states that if any in Column A is equal to 0 AND any in Column C is equal to 3, then Column X (X being the column where you enter this formula) is equal to 1, if not, then Column X is equal to 0.

To get the total, just add all the 1's in Column X.

Hope it makes sense.
 
Register on MacRumors! This sidebar will go away, and you'll see fewer ads.