Excel COUNTIF Compare To Another Cell Value

So in Excel, the COUNTIF function is very useful. However, I couldn’t figure out how to just use COUNTIF to only count cells if another cell is greater than 0. So here is how you do it:

=COUNTIF(Y12,">=" & $Z$3)

Basically, put your operator in quotes, then use the ampersand, followed by the cell that you want to compare to. Simple!

credit here

Advertisements

Excel shortcut for copying formulas

I found another quick Excel shortcut the other day.  (Courtesy of LifeHacker) When you want to copy a formula down a column, I typically grab the bottom right corner of a cell, and drag the mouse down through all the cells I want to copy the formula too.  This is pretty easy and fast, which is probably why I never found this new shortcut before.  An even quicker way is to simply double click on the bottom right corner of a cell, and it will automagically fill all the cells downward in the column until the last one with data in the column to the left.  Example:

Screen Shot 2013-06-28 at 6.45.04 AM

Here I have a column on the left with data, and the column on the right with a simple formula that I want to apply to that data.  Typically I would just grab the bottom right corner and drag down to copy the cell.

IMG_20130628_064555

An even faster way is to double click when the cursor changes to the black cross, and it will automagically copy the formula for you.

Screen Shot 2013-06-28 at 7.22.28 AM

Hope you found this useful and time saving!